Forum Discussion
Advocate IIIhow 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?
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.
Helper ITo get the distinct count you should simply be able to use a Dax measure.
Name count = Distinctcount(table[Name])
Advocate IIIYes, 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
Hi, use this dax in a calculated column:
Column = CALCULATE(DISTINCTCOUNT(Table2[Date]),FILTER(ALL(Table2),Table2[Name]=EARLIER(Table2[Name])))
SeanHere's an alternative... :smileyhappy:
Basically count the distinct dates per name
Times Attended COLUMN = CALCULATE ( DISTINCTCOUNT ( 'Table'[Date Attended] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) )Good Luck! :smileyhappy:
- LAndes
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
