Forum Discussion
Advocate IIIhow 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
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?
Name Count of Date Taken Distinct count of dates A-Alvarado, Yadira Yazmin 50 2500 Abboud, Kaizen 2 4 Abdullai, Mehram 35 1100 Abdullai, Shabnam 22 433 Abdurahman, Hassan 9 126 Abdurahman, Issam 42 2772 Abdurahman, Nawal 43 3139 Abebe, Dagmawi 56 3192 Abebe, Eliyabe 53 2862 Aburto, Julian 40 1600 Aceves, Aaron 16 256 Aceves, Jennifer 19 361 Acosta, Abraham 6 42 Adams, Anthony 5 35 Adams, CoraRose 15 225 Adams, Dallas 16 256 Nevermind.. It works! Thanks.
7 Replies
BustardoHelper I
To get the distinct count you should simply be able to use a Dax measure.
Name count = Distinctcount(table[Name])
LAndesYes, 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.
