Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to measure a distinct count across category and get total?

I have a table with two columns. First column contains IDs and second column contains a Type. I would like to calculate the total count of unique IDs across the type.

 

IDType
xxxxA
yyyyA
zzzzA
kkkkA
jjjjA
xxxxB
yyyyB
zzzzB

 

I tried following code:

 

calculate(DISTINCTCOUNT(Table2[ID]),ALLEXCEPT(Table2, Table2[Type]))

 

When I add a slicer for Type and select any one of the type, I get the correct distinct counts of IDs for a selected type (For example: When I select Type as B in the slicer, I get 3 unique count of IDs). But, when I clear the filter I get the answer as 5. I would like to get the answer as 8 because there are 8 unique IDs across the Type.

1 ACCEPTED SOLUTION

@Anonymous 

I am getting 8, can you check your data to see if it looks like mine

Fowmy_0-1632080243178.png

 

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

7 REPLIES 7
Fowmy
Super User
Super User

@Anonymous 

You can is this measure:

DCount = 
SUMX(
    SUMMARIZE( Table , Table[Type] ),
    DISTINCTCOUNT(Table[ID])
)
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

Anonymous
Not applicable

Using this, I am getting the answer as 10. I want to show 8 as an answer because there are 8 unique IDs across the Type.

@Anonymous 

I am getting 8, can you check your data to see if it looks like mine

Fowmy_0-1632080243178.png

 

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

Anonymous
Not applicable

Hey @Fowmy , Thanks for your reply. I have following table.

 

IDType
xxxxA
yyyyA
zzzzA
kkkkA
jjjjA
xxxxB
yyyyB
zzzzB

 

In your table, you missed ID "kkkk"

@Anonymous 

Try this 

DCount = 
SUMX(
    VALUES(Table3[Type] ),
    CALCULATE(DISTINCTCOUNT(Table3[ID]))
)
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

Anonymous
Not applicable

Hey @Fowmy  for nudging me in the right direction. I used the following function. 

 

SUMX(VALUES(Table2[Type]), CALCULATE(DISTINCTCOUNT(Table2[ID])))

aj1973
Community Champion
Community Champion

Hi @Anonymous 

a simple 

count of ID_ = COUNT(Table2[ID])
would it work?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors