cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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?

1 ACCEPTED SOLUTION
Super User

Hi,

This measure works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

Hi,

This measure works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User

@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!

Anonymous
Not applicable

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?

Solution Sage

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
Not applicable

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors