cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JR-BejeweledOne
Super User
Super User

CountRows with Filter exactin

I need to do a CountRows action on a filtered source but am getting an error.

 

CountRows(Filter(TrainingProjectsCumulative, Text(varTitle) exactin 'Project Name')) gives an 'Expected Table Value' error

 

CountRowsError.png

 

CountRows without the filter works fine.   If I change from exactin to = it tells me that it can't use that operator with that forumula.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
1 ACCEPTED SOLUTION

Accepted Solutions

I'm not sure then why its not working.  I've replicated the same setup using one of my projects and it works fine.  But let me ask another question.  Where are you using the formula?  The result of the countRows is going to be a number.  The result of the filter is a table of data.  Could the error be because you are trying to CountRows in a property that is looking for a Table?  In other words there is nothing wrong with the formula.  The problem may be where you are trying to use it.  Just add a label to your project and put this formula there.  See if that works.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

10 REPLIES 10
mdevaney
Super User
Super User

@JR-BejeweledOne 

There are two issues with your formula

CountRows(Filter(TrainingProjectsCumulative, Text(varTitle) exactin 'Project Name'))

 

1.  It is not necessary to use the Text function on something this is already text. 

Text(varTitle)

 

2.   You must introduce the table name into your formula to look through and entire column.

varTitle exactin your_datasource_name.'Project Name'

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Pstork1
Dual Super User
Dual Super User

I'm assuming Project Name is a single line of text column.  If that's true, then that's the issue.  ExactIn is expecting a collection as the target to be checked, but the column you are using is a single line of text.

 

If Project Name is a single line of text then I'm wondering why you are using Text() with varTitle?  Is the Project name a number?  If its just a text variable then your filter should look like this

Filter(TrainingProjectsCumulative, 'Project Name' = varTitle)

The column name has to come first when using the '=' 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Just a bit of additional information.

 

Text(varTitle) was due to the way varTitle was being set.  Set(varTitle, dropdown.selected.value).   Without the Text(varTitle) it would not properly filter the data source.   I have changed that.

 

The filter works properly as Filter(TrainingProjectsCumulative, varTitle exactin 'Project Name'), so I am not quite understanding why adding CountRows break it.

 

 

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

The filter works exactly as it should.  It's only when adding CountRows that it breaks.    And to your point, it is looking in a table for an exact title.  The difference between in and exactin is that in finds the match regardless of case and exactin is case sensitive.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

Unfortunately that does not work. 

 

If you look at the formula again you will see that the data source is there, as it's the first thing after Filter:

 

Filter(TrainingProjectsCumulative, varTitle exactin 'Project Name')

 

I tried both:

 

CountRows(Filter(TrainingProjectsCumulative, varTitle exactin TrainingProjectsCumulative.'Project Name'))

 

and

 

CountRows(Filter(varTitle exactin TrainingProjectsCumulative.'Project Name')

 

Both give an error.   As you can see TrainingProjectsCumulative is a Table.

 

CountRows1.png



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

I understand that TrainingProjectsCumulative is a datasource or collection, but what kind of column is 'Project Name'?  



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

It's a single line of text.   I am not sure why it matters.     The filter works by itself and doesn't complain.   It's only when I add Count rows per this post here that I get an error.  The error occurs using exactin and in.    I can't use = as it complains about that as well.

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/CountRows-with-a-filtered-column/td-p/57937

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

I'm not sure then why its not working.  I've replicated the same setup using one of my projects and it works fine.  But let me ask another question.  Where are you using the formula?  The result of the countRows is going to be a number.  The result of the filter is a table of data.  Could the error be because you are trying to CountRows in a property that is looking for a Table?  In other words there is nothing wrong with the formula.  The problem may be where you are trying to use it.  Just add a label to your project and put this formula there.  See if that works.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

I had tried it in a label and initially got the same error.  I probably typed something wrong.    I was using it in a gallery Items property.

 

I know better now.  Thank you for pointing me that direction.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (1,697)