Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, i have 2 tables. One is the history of services a technician did and a Calendar Table which links the Date in the Technician Services Table.
This is the structure
This is the date of the Services Table (the date is in format dd/mm/yyyy)
Service ID | Service Date | Technician |
1 | 01/03/2022 | John |
2 | 05/03/2022 | John |
3 | 05/03/2022 | John |
4 | 20/03/2022 | John |
5 | 02/04/2022 | John |
6 | 03/04/2022 | John |
7 | 15/04/2022 | John |
8 | 15/04/2022 | John |
9 | 25/04/2022 | John |
10 | 25/04/2022 | John |
11 | 25/04/2022 | John |
12 | 01/03/2022 | Bob |
13 | 04/03/2022 | Bob |
14 | 04/03/2022 | Bob |
15 | 09/03/2022 | Bob |
16 | 09/03/2022 | Bob |
17 | 21/03/2022 | Bob |
18 | 02/04/2022 | Bob |
19 | 15/04/2022 | Bob |
20 | 15/04/2022 | Bob |
21 | 26/04/2022 | Bob |
22 | 26/04/2022 | Bob |
23 | 26/04/2022 | Bob |
What I need is to know the quantity of days per month per technician that effectively worked. The values in bold red are the ones I need to obtain (Q Worked Days)
March | March | April | April | |
Technician | Q Services | Q Worked Days | Q Services | Q Worked Days |
Bob | 6 | 4 | 6 | 3 |
John | 4 | 3 | 7 | 4 |
Thanks!
Nicolás.
Solved! Go to Solution.
Hi @Nick1810 ,
You can try distinctcount function to count the working days for each people.
Measure:
Q Services = CALCULATE(COUNT(Services[Service Date]))
Q Worked Days = CALCULATE(DISTINCTCOUNT(Services[Service Date]))
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Nick1810 , what do you mean by effective here?
You can use count(Table[Service ID] )
or
countrows(summarize(Table, Table[Service Date]) )
Or you can filter for work day
example
measure =
calculate(count(Table[Service ID] ), filter(Date, weekday(Date[Date],2) < 6 ) )
Hi @amitchandak ,
By effective I mean how many days I technician worked despite how many services he did in one day.
I made a measure to count how many services a technician did, like the example you gave me, and that is how a got that Bob did 6 services in march and 6 in april, while John did 4 in march and 7 in april. I can calculate that.
But what I need to obtain is how many days each one worked in a month. If, for example, Bob did 5 services in 1 day, I want the measure to count 1.
Taking into account the example data I posted, Bob worked 4 days in March and 3 in April, while John worked 3 days in March and 4 in April. This is the data i want to calculate by a measure.
Thanks.
Hi @Nick1810 ,
You can try distinctcount function to count the working days for each people.
Measure:
Q Services = CALCULATE(COUNT(Services[Service Date]))
Q Worked Days = CALCULATE(DISTINCTCOUNT(Services[Service Date]))
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rzhou-msft ,
Yes, that worked! I was making it so complicated that wasn´t working. The thing I notice is that to work i don´t have to use Services[Service Date].DATE, but only Services[Service Date].
Many thanks!
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |