Count Days from a Table

[PBI5851]

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 ?

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Try this.  Drag Rep to the table visual and write this measure

=distinctcount(data[date])

Hope this helps.

[PBI5851]

@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. 

[Ashish_Mathur]

Hi,

Remove Rep from the visual and change the visual to a card visual.