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
burak
Frequent Visitor

Sum of Distinct Count by column

Hello Everyone,

 

I am having trouble calculating sum of Distinct Count. Basically, I would like to calculate Distinct count regarding a different column. Here is the example:

 

Term                ID

Spring 15          1

Spring 15          2

Spring 15          1

Spring 16          1

Spring 16          1

Spring 16          3

Spring 17          4

Spring 17          1

 

My original formula is DISTINCTCOUNT(Table[ID]). But it gives a total number 4. However, I would like to calculate sum of distinct ID for every term. It should have,

-2 distinct count for Spring 15,

-2 distinct count for Spring 16

-2 distinct count for Spring 17.

 

So distinct count would be 6 instead of 4.

 

By the way, there is something to point out. I was eligible to get correct number with a complicated formula but my page should react when we filter with a slicer. When I have something like (="Spring 15"), it does not response when Spring 15 is unselected in slicer.

 

It is not as complicated as it is seen. To recap, distinct count should be calculated for every data in column "term".

 

Sorry for the long message. I hope I can solve my issue.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

You could create a new column that concatenates Term and ID and then create a measure that does a DISTINCTCOUNT of that column. This measure should react appropriately to slicers and such. So:

 

Column

TermIDs =CONCATENATE([Term],[ID])

Measure

DistinctTermIDs = DISTINCTCOUNT([TermIDs])

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Adithya
New Member

I have a set of ids 

 

1

2

3

1

1

2

4

5

5

 

and i want the id and its count

1 - 3 times

2 - 2 times

3 - 1 time

4 - 1 time

5 - 2 times

 

and i have written a measure for the same

Measure = CONCATENATE(DISTINCT(Activityx[Column1.AppId]), DISTINCTCOUNT(Activityx[Column1.AppId]))

 

 

but i am unable to get the expected result. How can i achive that?

Adithya
New Member

I have a set of ids 

 

1

2

3

1

1

2

4

5

5

 

and i want the id and its count

1 - 3 times

2 - 2 times

3 - 1 time

4 - 1 time

5 - 2 times

 

and i have written a measure for the same

Measure = CONCATENATE(DISTINCT(Activityx[Column1.AppId]), DISTINCTCOUNT(Activityx[Column1.AppId]))

 

 

but i am unable to get the expected result. How can i achive that?

Greg_Deckler
Super User
Super User

You could create a new column that concatenates Term and ID and then create a measure that does a DISTINCTCOUNT of that column. This measure should react appropriately to slicers and such. So:

 

Column

TermIDs =CONCATENATE([Term],[ID])

Measure

DistinctTermIDs = DISTINCTCOUNT([TermIDs])

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello, 

I would like to ask question as a continue of your answer, when we calculate a measure

DistinctTermIDs = DISTINCTCOUNT([TermIDs])

How to put this measure result in new column for every product (row). 

 

Example, we have as a product name

Spring15 

Spring15 

Spring16

 

And create DISTINCTCOUNT measure that we can manually filter and for Spring 15 Total will return 2.

How to put this value 2 in the same table, near each item?

 

Result should be

ProductName   No. (from measure)

Spring15           2

Spring15           2

Spring16           1

 

Thank you in advance

Thanks , this helped me.

So how would you filter out a distict value?

 

So in the example above how would you say return the distinct values = SPRING15

 

I am surprised how fast you replied.

 

Thank you so much!

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.