cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LAndes Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Highlighted
LAndes Frequent Visitor
Frequent Visitor

Re: how to count distinct values in a column

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

LAndes Frequent Visitor
Frequent Visitor

Re: how to count distinct values in a column

Nevermind.. It works! Thanks.

View solution in original post

7 REPLIES 7
Bustardo Regular Visitor
Regular Visitor

Re: how to count distinct values in a column

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

 

Name count = Distinctcount(table[Name])

 

 

LAndes Frequent Visitor
Frequent Visitor

Re: how to count distinct values in a column

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 Super Contributor
Super Contributor

Re: how to count distinct values in a column

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Sean Super Contributor
Super Contributor

Re: how to count distinct values in a column

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

Highlighted
LAndes Frequent Visitor
Frequent Visitor

Re: how to count distinct values in a column

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

LAndes Frequent Visitor
Frequent Visitor

Re: how to count distinct values in a column

Nevermind.. It works! Thanks.

View solution in original post

Microsoft v-huizhn-msft
Microsoft

Re: how to count distinct values in a column

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors