Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.