Skip to main content
cancel
Showing results for
Search instead 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

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

 Name ID xxx 1234 xxx 2514 bbb 1234 bbb 3569 aaa 1234

ArticleCode table:

 ArticleCode ID aaaaaaaaaa 1234 ssssssssssss 1234 aaaaaaaaaa 3569 bbbbbbbb 2514

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
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])``

@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
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])``

@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
New Member

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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors