Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 |
To get the distinct count you should simply be able to use a Dax measure.
Name count = Distinctcount(table[Name])
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.
Hi, use this dax in a calculated column:
Column = CALCULATE(DISTINCTCOUNT(Table2[Date]),FILTER(ALL(Table2),Table2[Name]=EARLIER(Table2[Name])))
Here's an alternative...
Basically count the distinct dates per name
Times Attended COLUMN = CALCULATE ( DISTINCTCOUNT ( 'Table'[Date Attended] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) )
Good Luck!
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.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |