Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hello everyone,
My table follows this structure:
Item | Code | Class |
FFF | 15.1 | A |
FFF | 16.1 | B |
YYY | 15.1 | A |
YYY | 15.1 | A |
YYY | 20.1 | A |
XXX | 16.1 | C |
As you can see, an item can appear more than once in the table. The code can also be repeated. I want to create a measure that counts the distinct Code types for each distinct Item.
So, based on the example table above, the measure should bring me de value 5, because:
FFF has 2 distinct codes (15.1 and 16.1)
YYY has 2 distinct codes (15.1 and 20.1)
XXX has 1 distinct code (16.1)
So 2 + 2 + 2 = 5
Can someone help me?
Solved! Go to Solution.
Hi,
This measure works
=SUMX(SUMMARIZE(VALUES(Data[Item]),Data[Item],"ABCD",DISTINCTCOUNT(Data[Code])),[ABCD])
Hope this helps.
Hi,
This measure works
=SUMX(SUMMARIZE(VALUES(Data[Item]),Data[Item],"ABCD",DISTINCTCOUNT(Data[Code])),[ABCD])
Hope this helps.
You can just use this measure expression, replacing Table with your actual table name.
Distinct Item and Code = COUNTROWS(SUMMARIZE(Table, Table[Item], Table[Code]))
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Anonymous
you can try this
Measure =
VAR _TBL=SUMMARIZE('Table','Table'[Item],"_count",DISTINCTCOUNT('Table'[Code]))
return SUMX(_TBL,[_count])
Proud to be a Super User!
Hey @ryan_mayu
I tried to create this measure of yours here, but it gives me an error in the "return" part (unexpected expression). How can I create your measurement without this error happening to me?
If you just do a distinctcount on the Code column, then stick the Item column as rows in a matrix, it should give you the results you want?
Hi @jthomson
I'm sorry, I didn't understand the matrix part. Would that be a measure created?
Check out the May 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
72 | |
71 | |
51 | |
47 |
User | Count |
---|---|
45 | |
38 | |
33 | |
30 | |
28 |