Forum Discussion

LAndes's avatar
LAndes
Icon for Advocate III rankAdvocate III
9 years ago
Solved

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...
Need Help
LAndes's avatar
LAndes
Icon for Advocate III rankAdvocate III
9 years ago

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
LAndes's avatar
LAndes
Icon for Advocate III rankAdvocate III
9 years ago

Nevermind.. It works! Thanks.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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