Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to do a double DISTINCTCOUNT?

Hello everyone,
My table follows this structure:

ItemCodeClass
FFF15.1A
FFF16.1B
YYY15.1A
YYY15.1A
YYY20.1A
XXX16.1C

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
Ashish_Mathur
Super User
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/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
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/
mahoneypat
Employee
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!

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


@mahoneypa HoosierBI on YouTube


ryan_mayu
Super User
Super User

@Anonymous 

you can try this

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

1.PNG





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?

jthomson
Solution Sage
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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