Forum Discussion
Count Days from a Table
Hello,
I have a requriement to capture how many days a rep is active per month. The rep can have multiple rows in a single day as there are other fields (such as status and categories etc).
| JobID | Date | Rep |
| J223 | 9/1/2020 | Mak |
| J224 | 9/1/2020 | Steve |
| J225 | 9/1/2020 | Shawn |
| J226 | 9/1/2020 | Mak |
| J227 | 9/2/2020 | Jennifer |
| J228 | 9/3/2020 | Shawn |
| J229 | 9/3/2020 | Mary |
| J230 | 9/3/2020 | Mary |
| J231 | 9/3/2020 | Shawn |
| J232 | 9/5/2020 | Mal |
| J233 | 9/5/2020 | Mak |
| J234 | 9/5/2020 | Mak |
| J235 | 9/5/2020 | Mak |
| J236 | 9/5/2020 | Shawn |
| J237 | 9/5/2020 | Steve |
| J238 | 9/10/2020 | Jennifer |
| J239 | 9/10/2020 | Mary |
| J240 | 9/10/2020 | Keith |
I intend to get the below result
| Jennifer | 2 |
| Keith | 1 |
| Mak | 2 |
| Mal | 1 |
| Mary | 2 |
| Shawn | 3 |
| Steve | 2 |
Which shows the unique number of days the rep has been active. I was thinking of distinct count of days, but not sure how to count that. When i do a count(date), i get a ridiculously long number.
Any other recommendations on how to achieve this ?
4 Replies
Hi,
Try this. Drag Rep to the table visual and write this measure
=distinctcount(data[date])
Hope this helps.
Ashish_Mathur , is there a way to achieve this without displaying the Rep Name. There is a visual need to do a company target and dept target. So i should get 13 for the month and display just that value on a card or subsequent visuals with further calculations.
Hi,
Remove Rep from the visual and change the visual to a card visual.
Hello to PBI5851,
There are two ways to get the effect of your question request:
1. Create a column
Month_day = CALCULATE(DISTINCTCOUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Rep]))Result:
2. Create a measure and form a card to view it.
Measureļ¼ Count = DISTINCTCOUNT('Table'[Date])Form a Rep-based cutter and a custom-based Card:
Result:
Best regards
Liu Yang
If this post helps,then consider Accepting it as the solution to help other members find it faster.
