cancel
Showing results for 
Search instead for 
Did you mean: 
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

View solution in original post

Nevermind.. It works! Thanks.

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.