cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lopez0090
Helper II
Helper II

DAX to sum the values of "Distinctcount".

Suppose you have the following data, and you are creating a relationship with the Calendar table.

51507.jpg

 

The matrix visual uses DAX ("measure") to count the unique number of each Category.

measure = DISTINCTCOUNT(Table1[category])

 

Currently, we are showing the overall number of unique values (A, B, C = 3), but we would originally like to show the total number of unique values(3+2+2+1=8) for each month.

151834.jpg  52740.jpg

 

If you have any good ideas on how to make this happen, could you please let me know?

 

 

Best regards,

Lopez

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Lopez0090 

Please try this measure

Dcount = 
SUMX(
    SUMMARIZE(Table1,Table1[Year],Table1[Month Number]),
    calculate(DISTINCTCOUNT(Table1[Category]))
)

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@Lopez0090 

Please try this measure

Dcount = 
SUMX(
    SUMMARIZE(Table1,Table1[Year],Table1[Month Number]),
    calculate(DISTINCTCOUNT(Table1[Category]))
)

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Genius! Thank you so much.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.