Help with DistinctCount by Date

[adoster]

Trying to find the distinct count of procedures by date.

Date	Name	Procedure Display Name	Distinct PDN
11/8/2021	Mickey Mouse	Procedure A	1
11/8/2021	Mickey Mouse	Procedure B	1
11/9/2021	Mickey Mouse	Procedure A	1
11/9/2021	Mickey Mouse	Procedure B	1

Desired Result:

Date	Distinct Count of PDN
11/8/2021	1
11/9/2021	1
Total	2

I tried a simple measure doing a discount count on the NAME which returns a value of 1

Measure1 = DISTINCTCOUNT('Table'[Name])

Desired Results with Multiple People across 3 days

Date	Name	Procedure Display Name	Distinct PDN
11/8/2021	Donald Duck	Procedure A	1
11/8/2021	Donald Duck	Procedure B	1
11/8/2021	Mickey Mouse	Procedure A	1
11/8/2021	Mickey Mouse	Procedure B	1
11/9/2021	Donald Duck	Procedure A	1
11/9/2021	Donald Duck	Procedure B	1
11/9/2021	Mickey Mouse	Procedure A	1
11/10/2021	Mickey Mouse	Procedure A	1
		TOTAL	5

[adoster]

I believe that I need distinct count based on Name & Procedure.

If one Name has 5 procedures on 1 day = 1 volume

If two Names each have 5 procedures on 1 day = 2 volume

If two Names each have 5 procedures on 2 days = 4 volume

If three Names each have 5 procedures on 2 days = 6 volume

In the first example above I did a Distinct Count on Name. The Total count give me 1 instead of the desired 2

Hope this helps

[v-rzhou-msft · Accepted solution]

Hi @adoster 

Try to create this measure to achieve your goal.

Measure = 
VAR _SUMMARIZE = SUMMARIZE('Table','Table'[Date],"DISTINCT COUNT",DISTINCTCOUNT('Table'[Name]))
RETURN
SUMX(FILTER(_SUMMARIZE,[Date]<= MAX('Table'[Date])),[DISTINCT COUNT])

Result is as below.

Best Regards,
Rico Zhou

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

[adoster]

Hi RicoZhou,

I ran across one snag on the solution I was wondering if you might be able to help.

The Table View shows the correct total count (5) that I was looking for, but it doesn't work on a Graph Visualization. It still shows the total procedure count (8)

To try and solve this I added a column which gives a Rank to each Procedure Name. My thought is that the Graph would only count the highest rank and then ignore everything after.

Sorry if this is a totally separate issue and I can create a new post if needed 🙂

Example of volume that would show on a graph:

Date	Name	Procedure Display Name	Distinct PDN	Rank
11/8/2021	Donald Duck	Procedure A	1	1
11/8/2021	Donald Duck	Procedure B	1	2
11/8/2021	Mickey Mouse	Procedure A	1	1
11/8/2021	Mickey Mouse	Procedure B	1	2
11/9/2021	Donald Duck	Procedure A	1	1
11/9/2021	Donald Duck	Procedure B	1	2
11/9/2021	Mickey Mouse	Procedure A	1	1
11/10/2021	Mickey Mouse	Procedure B	1	1

		Desired Results
	Procedure Display Name	Count by Rank
11/8/2021	Procedure A	2
11/8/2021	Procedure B	0
11/9/2021	Procedure A	2
11/9/2021	Procedure B	0
11/10/2021	Procedure A	0
11/10/2021	Procedure B	1
Total		5

Thanks for any assistance.

[adoster]

This worked! Thank you so much!

[lbendlin]

You need to decide what you are actually trying to measure. Distint count of name, distinct count of procedure, or distinct count of [Name]+[Procedure]  ?