Distinct Count and Row Count Help

[chris886]

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

[v-yilong-msft · Accepted solution]

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.

[tackytechtom]

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/

[chris886]

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!