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
LAndes
Advocate III
Advocate III

how to count distinct values in a column

Hi all,


I have a table that looks like this:

Name     Date Attended

Suzy      1/1/17

Suzy      1/2/17

John     2/4/17

John 5/2/17

etc

 

I want to ultimately  understand how many times each person attended and make categories such as 1-10 x, 11-20x etc.   while I realize i can make a custom visualization with distinct count of Date taken by Name. I would like to create a column that does the same thing that I can use to create categories.  Make sense?

Thanks

2 ACCEPTED SOLUTIONS

Thank you, I have tried both of those and it still is not producing the right values:

 

Here is the results: Count of Date taken, is just using Distinct Count via the quick menu in the visualisation,  The column labeled Distinct count of dates uses this formula Distinct count of dates = CALCULATE(DISTINCTCOUNT(Services[Date Taken].[Date]), FILTER(ALL(Services),Services[Participant Enterprise Identifier]=EARLIER(Services[Participant Enterprise Identifier])))..

 

The Participant Enterprise Identifier is just the ID number..  Any thoughts?

 

 

NameCount of Date TakenDistinct count of dates
A-Alvarado, Yadira Yazmin502500
Abboud, Kaizen24
Abdullai, Mehram351100
Abdullai, Shabnam22433
Abdurahman, Hassan9126
Abdurahman, Issam422772
Abdurahman, Nawal433139
Abebe, Dagmawi563192
Abebe, Eliyabe532862
Aburto, Julian401600
Aceves, Aaron16256
Aceves, Jennifer19361
Acosta, Abraham642
Adams, Anthony535
Adams, CoraRose15225
Adams, Dallas16256

View solution in original post

Nevermind.. It works! Thanks.

View solution in original post

7 REPLIES 7
Bustardo
Helper I
Helper I

To get the distinct count you should simply be able to use a Dax measure.

 

Name count = Distinctcount(table[Name])

 

 

Yes, that is of course what I tried but it is not providing the right answer:

 

If I just use the "distinct count function" when I put in the Date Taken field into a table, I get the right number.. I create a Column with the following formula

 

Distinct count of dates = DISTINCTCOUNT(Services[Date Taken].[Date])

 

It is not providing the right values.

 

 

 

Vvelarde
Community Champion
Community Champion

@LAndes

 

Hi, use this dax in a calculated column:

 

 

Column = CALCULATE(DISTINCTCOUNT(Table2[Date]),FILTER(ALL(Table2),Table2[Name]=EARLIER(Table2[Name])))

 

DC.png




Lima - Peru
Sean
Community Champion
Community Champion

Here's an alternative... Smiley Happy

Basically count the distinct dates per name

Times Attended COLUMN = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date Attended] ),
    ALLEXCEPT ( 'Table', 'Table'[Name] )
)

Good Luck! Smiley Happy

Times Atteneded COLUMN per Name.png

Thank you, I have tried both of those and it still is not producing the right values:

 

Here is the results: Count of Date taken, is just using Distinct Count via the quick menu in the visualisation,  The column labeled Distinct count of dates uses this formula Distinct count of dates = CALCULATE(DISTINCTCOUNT(Services[Date Taken].[Date]), FILTER(ALL(Services),Services[Participant Enterprise Identifier]=EARLIER(Services[Participant Enterprise Identifier])))..

 

The Participant Enterprise Identifier is just the ID number..  Any thoughts?

 

 

NameCount of Date TakenDistinct count of dates
A-Alvarado, Yadira Yazmin502500
Abboud, Kaizen24
Abdullai, Mehram351100
Abdullai, Shabnam22433
Abdurahman, Hassan9126
Abdurahman, Issam422772
Abdurahman, Nawal433139
Abebe, Dagmawi563192
Abebe, Eliyabe532862
Aburto, Julian401600
Aceves, Aaron16256
Aceves, Jennifer19361
Acosta, Abraham642
Adams, Anthony535
Adams, CoraRose15225
Adams, Dallas16256

Nevermind.. It works! Thanks.

Hi @LAndes,

Congratulations, you have resolved your issue. Please mark the right reply as answer or welcome to share your own workaround, so  people will learn and benefit more from here.

Best Regards,
Angelia

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.