How to do a double DISTINCTCOUNT?

[Anonymous]

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?

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

=SUMX(SUMMARIZE(VALUES(Data[Item]),Data[Item],"ABCD",DISTINCTCOUNT(Data[Code])),[ABCD])

Hope this helps.

[mahoneypat]

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

[ryan_mayu]

@Anonymous 

you can try this

Measure = 
VAR _TBL=SUMMARIZE('Table','Table'[Item],"_count",DISTINCTCOUNT('Table'[Code]))
return SUMX(_TBL,[_count])

[Anonymous]

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?

[jthomson]

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?

[Anonymous]

Hi @jthomson 
I'm sorry, I didn't understand the matrix part. Would that be a measure created?