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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
eehp
New Member

Sum of Count of distinct occurence for each distinct ID

Hello, I'm struggelling with a problem since more than 16 hours now...
I have 2 table with many to many link  between Name and ArticleCode
Name table look like that

NameID
xxx1234
xxx2514
bbb1234
bbb3569
aaa1234


ArticleCode table:

ArticleCodeID
aaaaaaaaaa1234
ssssssssssss1234
aaaaaaaaaa3569
bbbbbbbb2514

 

I would like to count the number of unique ArticleCode found for each unique Name and sum it in one measure to be display in a card.
Any ideas how to do it ? I'd try distinctcount, sum, sumx, count and various combination already...


1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@eehp What's the answer for the supplied data? I get 7 using this:

Measure = SUMX(SUMMARIZE('Table',[Name],"__Count",COUNTROWS(DISTINCT('Table2'[ArticleCode]))),[__Count])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@eehp What's the answer for the supplied data? I get 7 using this:

Measure = SUMX(SUMMARIZE('Table',[Name],"__Count",COUNTROWS(DISTINCT('Table2'[ArticleCode]))),[__Count])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

For the supplied data it's 7 yes. I'll check on the whole dataset and valide the answer if correct but it's look promising to me 😄

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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