Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
markmess77
Resolver I
Resolver I

Calculate distinct count of ID based on minimum value of field

I am working with a table similar to the below:

 

IDDateCategory
12312 Feb, 2021  A
12310 Feb, 2021  B
45620 Mar, 2021  B
45622 Mar, 2021  A
45622 Mar, 2021  B
78925 Feb, 2021  A

 

I want to display the distinct count of the ID field by category. However, some ID's fall into two different categories so the column totals do not add up to the distinct total value.

Currently my result looks something like

Category  Distinct Count
A  3
B

  2

TOTAL

  3

Since different instances of an ID can fall into more than one category, the column totals do not add up to the correct distinct total amount. As seen from the first table, ID 123 and ID 456 fall into two different categories so their count is added twice - one for each category. Thus manually adding the Distinct Count column (5) is greater than the distinct total (3).

In this context, I am only interested in the Category of the minimum date Date of each ID. So I tried the following measure to return the distinct count of each ID, for only their associated minimum dates.

 

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table[ID] ),
    FILTER ( ALL ( Table[Date] ), MIN ( Table[Date] ) )
)

 

I then plotted this measure against the categories and still received the same result as before. I know something is wrong with the FILTER portion of the measure, perhaps the ALL call, but I have not been able to get something to work.

1 ACCEPTED SOLUTION
markmess77
Resolver I
Resolver I

Ended up figuring it out with an alternate solution. Instead of using a measure, I created a new table using the SUMMARIZE, aggregating by the minimum date value. 

View solution in original post

1 REPLY 1
markmess77
Resolver I
Resolver I

Ended up figuring it out with an alternate solution. Instead of using a measure, I created a new table using the SUMMARIZE, aggregating by the minimum date value. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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