cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resolver I

## Help with DistinctCount by Date

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

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Resolver I

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

Community Support

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.

Resolver I

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.

Resolver I

This worked! Thank you so much!

Super User

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]  ?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors