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

Count number of rows after a filter is applied

 

Hi,

 

I've been trying to find a solution with the below scenario. I hope someone can help.

 

I want to get the number of rows in Table1 filtered by the "Value" field in Table2. I've created a custom column in Table2 to input the values. 

I use the code "Count of Value = CALCULATE(COUNTROWS(Table1),FILTER('Table1','Table1'[Value]=Table2[Value]))" and it gets what I want. However, if I do filters on Table1 on the report, the values do not change. How do I link the values based on the filters selected?

 

Thanks,

Ashley

 

Table1.PNGTable2.PNG

1 ACCEPTED SOLUTION

@aiweller,

Instead of count of rows as a calculated column, try creating a measure as follows

 

Count of Values = CALCULATE(COUNTROWS(Table3), FILTER(Table3, Table3[Value] = MAX(Table4[Value]) ))

This should give you the desired result

 

Regards,

Thejeswar

View solution in original post

8 REPLIES 8
Thejeswar
Memorable Member
Memorable Member

Hi @aiweller,

 

When you say "However, if I do filters on Table1 on the report, the values do not change" what do you mean?

 

From your post, I get that Table 1 is your source and Table 2 has a calculated column which is based on Table 1.

Now you are adding a filter on Table1 and your Table 1 is not getting filtered

 

Can you clarify a bit more on what is the filter you are talking about and what are the values?

 

It's better if you can give some sample data and your expected result

 

Regards,

Thejeswar

Hi @Thejeswar,

 

Thanks for the reply.. What I mean is if I filter let's say, Country I only select "US" or Department I only select "Finance", the values in the calculated column in Table2 does not get updated. I would like to have the Table2 column show the number of rows resulting from the filter.

 

For example, if I select County "US" in Table1, the number in "Count of Value" in Table2 should only show values "3A" as 1 and "1B" as 1. The rest is 0.

 

Hope this explains.

 

Ashley

Hi @aiweller

 

Do you have a relationship between your two tables ? If yes you need to change it so it become a bidirectionnal relation

Go to the relationtab, double blick on the link between the tables and change the direction

 

It should work 

 

- Quentin

Hi @quentin_vigne,

 

Thanks for the suggestion. If I do a relationship the values that do not have matching records do not show. I would like to have values that do not have any matching records as "0".

 

For instance if I do relationship between the tables based on the "Value" column from both tables, the value for "1A" will not show. It will look like below. Unless there is another way to display unmatched records on Table2. 🙂

 

relationship.PNG

 

Thanks,

Ashley

 

 

@aiweller

 

I've done this and it looks like it's working

 

 1st.PNG2nd.PNG

 

 

I've added Value and Count of Table2 on the table 

 

If you want to see 0 instead of blank add "+ 0" at the end of your formula that count row

Hi @quentin_vigne,

 

From your example, you have selected "US" and the departments "Finance" and "HR" is displayed, but the "Count of Values" is still counting the total number of "2A" in Table1. The expected result for "2A" should be 1 instead of 2(it is showing 2 because it's including the value from "France").

 

relationship2.PNG

 

 

Thanks,

Ashley

 

@aiweller,

Instead of count of rows as a calculated column, try creating a measure as follows

 

Count of Values = CALCULATE(COUNTROWS(Table3), FILTER(Table3, Table3[Value] = MAX(Table4[Value]) ))

This should give you the desired result

 

Regards,

Thejeswar

View solution in original post

Hi @Thejeswar,

 

Thanks so much. Works perfectly as what I wanted to achieve.

 

Just to ask, do you know why the total is not showing? In the result below, the second table I added (+0) to the end of the code to show the rows that have no matching records.

 

count0.png

 

Regards,

Ashley

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!