cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors