cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jofoster
Microsoft
Microsoft

Count rows in a table visual that is filtered by a count

I have a table visual that is showing data for all the users who have more than 1 Team.  I filter that visual to only show the data where the count of teams is greater than 1. I want to get a count of the rows in the filtered table visual.  Feels like this should be really easy, but my internet searches are not helping.  Looking at the table below, I want to count the number of users

 

 

UserCount
user 13
User 24
User 32
  
4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @jofoster ,

I created some data:

vyangliumsft_0-1628747011374.png

Here are the steps you can follow:

1. Create measure.

Measure = COUNTROWS(ALLSELECTED('Table_Count')

2. Result:

When there is no filtering, the total number is displayed:

vyangliumsft_1-1628747011376.png

When the filter Count> 1, the number of rows after the filter is displayed:

vyangliumsft_2-1628747011380.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

@jofoster  Are you still having trouble with this? Can you please update the community either way and let us know how you're getting on?

 

@Eyelyn9  Why have you marked @v-yangliu-msft 's post as the solution? I do agree that Liu Yang gave much more images and detail, but that does not make their response a solution. @jofoster has not provided enough information about their dataset for us to know if @v-yangliu-msft 's measure will work. The measure @v-yangliu-msft provided will only work if the data @jofoster is working with is already summarized and grouped by User, which in most datasets this is not the case. 

 

In the post by @v-yangliu-msft there is only 1 row per user in the raw data, so the measure they have provided will work in this very specific use case: 

 

AllisonKennedy_0-1629367091850.png

 

However, if I generate more realistic data which has multiple rows per user, but only 5 users: 

AllisonKennedy_1-1629367191732.png

This gives the table visual that @jofoster mentioned in their original post: 

 

AllisonKennedy_2-1629367231567.png

However, using the @v-yangliu-msft will count ALL the ROWS, not ALL the USERS. 

 

As I mentioned in my original post, this problem will be very easy if there is a UserTable: 

AllisonKennedy_3-1629367330357.png

Here is more info on Dim tables for reference: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html


I haven't heard from @jofoster with any clarification or questions, so can't know what their data looks like or if this is solved or if they already have a user table dimension. 

 

Please see attached file below my signature for the detailed solution, and more expanded to work for Grand Total even when user is filtered in visual. It also shows why I have made the difficult decision to unmark the @v-yangliu-msft post as a solution, because I do not want others to be mislead and get confused. As you can see in the image below, their post returns 9 total users, when there are only 5. Even if I filter the table, it still returns the incorrect value of 7: 

 

AllisonKennedy_4-1629367580400.png

 

Everything depends on the data model, for reference here is the data model I have used: 

 

AllisonKennedy_5-1629367608775.png

 

Thanks to @v-yangliu-msft  for your post and detailed images and help.

 

@jofoster  Please do let us know if this is resolved.

 

Thanks!


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Oh, and the measure that answers the question with filters already included: 

 

Count Users with more than 1 Team =
COUNTROWS(
FILTER(Users, [Team Count] > 1)
)

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

Create a measure for Count Users:

 

Count Users = DISTINCTCOUNT(Usertable[User])

 

This should respond to any filters you apply. 

 

If all your data is in one table though and you don't have a User table you'll need more DAX. Please send screenshot of your model relationships veiw if that is the case.


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.