Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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?
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |