The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
ID | Type |
xxxx | A |
yyyy | A |
zzzz | A |
kkkk | A |
jjjj | A |
xxxx | B |
yyyy | B |
zzzz | B |
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.
Solved! Go to Solution.
@Anonymous
I am getting 8, can you check your data to see if it looks like mine
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
You can is this measure:
DCount =
SUMX(
SUMMARIZE( Table , Table[Type] ),
DISTINCTCOUNT(Table[ID])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hey @Fowmy , Thanks for your reply. I have following table.
ID | Type |
xxxx | A |
yyyy | A |
zzzz | A |
kkkk | A |
jjjj | A |
xxxx | B |
yyyy | B |
zzzz | B |
In your table, you missed ID "kkkk"
@Anonymous
Try this
DCount =
SUMX(
VALUES(Table3[Type] ),
CALCULATE(DISTINCTCOUNT(Table3[ID]))
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hey @Fowmy for nudging me in the right direction. I used the following function.
SUMX(VALUES(Table2[Type]), CALCULATE(DISTINCTCOUNT(Table2[ID])))
Hi @Anonymous
a simple
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