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

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

v-huizhn-msft Super Contributor
Super Contributor

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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 449 members 3,820 guests
Please welcome our newest community members: