Forum Discussion
How to do a double DISTINCTCOUNT?
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?
Hi,
This measure works
=SUMX(SUMMARIZE(VALUES(Data[Item]),Data[Item],"ABCD",DISTINCTCOUNT(Data[Code])),[ABCD])
Hope this helps.
6 Replies
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?
Anonymous
you can try this
Measure = VAR _TBL=SUMMARIZE('Table','Table'[Item],"_count",DISTINCTCOUNT('Table'[Code])) return SUMX(_TBL,[_count])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?
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
Hi,
This measure works
=SUMX(SUMMARIZE(VALUES(Data[Item]),Data[Item],"ABCD",DISTINCTCOUNT(Data[Code])),[ABCD])
Hope this helps.
