cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shaun_Hendricks
Level: Powered On

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
Shaun_Hendricks
Level: Powered On

Re: Calculated Column returns error when using Filter () fuction

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
Shaun_Hendricks
Level: Powered On

Re: Calculated Column returns error when using Filter () fuction

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 Team
Community Support Team

Re: Calculated Column returns error when using Filter () fuction

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.
Shaun_Hendricks
Level: Powered On

Re: Calculated Column returns error when using Filter () fuction

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.

Shaun_Hendricks
Level: Powered On

Re: Calculated Column returns error when using Filter () fuction

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

Error under Discussion

Highlighted
Community Support Team
Community Support Team

Re: Calculated Column returns error when using Filter () fuction

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.
Shaun_Hendricks
Level: Powered On

Re: Calculated Column returns error when using Filter () fuction

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
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,588)