cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
https://www.linkedin.com/in/excelenthusiasts/
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
https://www.linkedin.com/in/excelenthusiasts/
Microsoft

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Super User

@Anonymous

you can try this

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

Did I answer your question? Mark my post as a solution!

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?

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors