Forum Discussion
Distinct Count and Row Count Help
Here's my sample data
| Row Number | Name |
1 | Chris |
| 1 | Dave |
| 1 | Adam |
| 2 | Chris |
| 2 | Evan |
| 3 | Dave |
| 3 | Evan |
| 3 | George |
Here are the results I'm looking for, I know that I can use Distinct Count of Row Number to get the event total for each name, but I also want the Total to show the distinct number of Names being filtered.
| Name | Events |
| Chris | 2 |
| Dave | 2 |
| Adam | 1 |
| Evan | 2 |
| George | 1 |
| TOTAL NAMES | 5 |
Hi chris886 ,
I create a table as you mentioned.
Then I create a new table and I also use the DAX code as tackytechtom mentioned.
Table 2 = SUMMARIZE ( 'Table', 'Table'[Name], "Events", COUNTROWS ( 'Table' ) )Measure = IF(ISFILTERED('Table'), DISTINCTCOUNT('Table'[Row Number]), DISTINCTCOUNT('Table'[Name]) )Finally when you add a slicer, you will get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi chris886 ,
How about this:
Here the measure:
Measure = IF(ISFILTERED('Table'), DISTINCTCOUNT('Table'[Row Number]), DISTINCTCOUNT('Table'[Name]) )Let me know, if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Well, what you suggested did work, but only when no other filters were applied. My source table has many additional data columns. When I tried applying a filter/slicer to one of those, this table goes back to counting total rows of names (8 in sample) versus distinct names (5).
So my fault for trying to simplify the issue and not provide more context. Is there a tweak to the above DAX that would account for additional filters being applied to the table or page as a whole?
Thanks!
Hi chris886 ,
I create a table as you mentioned.
Then I create a new table and I also use the DAX code as tackytechtom mentioned.
Table 2 = SUMMARIZE ( 'Table', 'Table'[Name], "Events", COUNTROWS ( 'Table' ) )Measure = IF(ISFILTERED('Table'), DISTINCTCOUNT('Table'[Row Number]), DISTINCTCOUNT('Table'[Name]) )Finally when you add a slicer, you will get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
