cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculated Column returns error when using Filter () fuction

I'm working with a SharePoint List. I have a Calculated column that returns an integer based on a DateDiff calculation. The Column works perfectly in SharePoint. When I reference the list in PowerApp with a Filter, Filtering on the column, I get an error "Invalid argument type. Expecting Number Value". I've tried using the Value() statement to force the number into the filter but that didn't work either. Here's the filter:
Filter( Table, 5 <= ColumnName )
Messed with spacing, with order of comparison "ColumnName >= 5" etc. No difference. Is it because it's a Calculated Column?

1 ACCEPTED SOLUTION

Accepted Solutions

Nope, the forumla change is not correct, by sheer luck (and sleuthing of the pop up notifications) I stumbled across the answer.  THIS is the correct formula:

 

Filter(Turnouts, (Value(ExpirationCounter)) >= 5, (Status.Value) <> "End of Life")

 

You have to have the comma after the List (or lookup item name) because it's required by the syntax of the Filter command.  The paranthesis around the Value function is required because it must be evaluated FIRST before it can do the math that follows, so it represents a single number to evaluate.  Thus it looks up the column, then generates the Value, then compares the statement.  The comma means another logical test follows.  Now, here's where I stumbled upon the solution that let me fix the first statement.  I couldn't even get the Status column to evaluate correctly but for giggles I put in some more normal dotted code and POOF! all my indicators went away after I put it in paranthesis.  I did that on the first statement and it works great.

 

So, lesson is, try about everything and don't trust Microsoft documentation because it falls a bit short on the stuff you can actually use in a given situation.  The prompting could be a bit better but what was there did help me sleuth it out.  Far from Elementary dear Watson!

View solution in original post

6 REPLIES 6
Frequent Visitor

Over 20 views and nada eh?  I had hoped that maybe the forums might work better than the comments section of the documentation area on Filters but looks like about the same result.  Perhaps PowerApps is yet another product Microsoft launched that they're just going to abandon since a product is only as good as its support.  Add it to the pile I guess.

Community Support
Community Support

Hi @Shaun_Hendricks,

 

Would you please share a screenshot for the calculated column formula used under SharePoint?

My testing as below:

Column formula:

DateDif([Date1], [Date2],"D")

 

Then within PowerApps, I use the following formula to perform the query:

Filter(Training, Value(DateCal)<=25)

This would show the results, but with a blue reminding, which is the delegation issue:

Delegable data sources

Currently only "=" is supported for delegation, for the other known issues when working with SharePoint list in PowerApps, see:

Connect from Microsoft PowerApps to SharePoint

 

PowerApps would be able to read data from calculated column, but it seems the field type is not clearly identified.

Using value() function would make the formula work from my side.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for responding.  Here's the column formula:

 

=DATEDIF([Manufacture Date],TODAY(),"Y")

 

It just returns the number of years a given piece of equipment is in service.  I'm trying to filter based on anything that is over 5 years old with the PowerApp.

Here's the actual error code when I hover over the "ExpirationCounter" field which is the Column Name in Sharepoint.

Error under Discussion

Hi @Shaun_Hendricks,

 

Please switch your formula in the following way:

 

Filter(Turnouts 5<=value(ExpirationCounter),Status<>"End of Life")

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Nope, the forumla change is not correct, by sheer luck (and sleuthing of the pop up notifications) I stumbled across the answer.  THIS is the correct formula:

 

Filter(Turnouts, (Value(ExpirationCounter)) >= 5, (Status.Value) <> "End of Life")

 

You have to have the comma after the List (or lookup item name) because it's required by the syntax of the Filter command.  The paranthesis around the Value function is required because it must be evaluated FIRST before it can do the math that follows, so it represents a single number to evaluate.  Thus it looks up the column, then generates the Value, then compares the statement.  The comma means another logical test follows.  Now, here's where I stumbled upon the solution that let me fix the first statement.  I couldn't even get the Status column to evaluate correctly but for giggles I put in some more normal dotted code and POOF! all my indicators went away after I put it in paranthesis.  I did that on the first statement and it works great.

 

So, lesson is, try about everything and don't trust Microsoft documentation because it falls a bit short on the stuff you can actually use in a given situation.  The prompting could be a bit better but what was there did help me sleuth it out.  Far from Elementary dear Watson!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (11,528)