The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Dear experts,
I am trying to calculate the number of sick days per month.
The challenge is that there are instances where an employee has 2 lines per day (due to the shift distribution) like this:
WorkDay | Salary ID | Timesheet | WorkTimeStart | WorkTimeEnd |
31-12-2019 | 1234 | Illness | 31-12-2019 06:30 | 31-12-2019 09:00 |
31-12-2019 | 1234 | Illness | 31-12-2019 13:00 | 31-12-2019 15:30 |
30-12-2019 | 1234 | Normal | 30-12-2019 06:30 | 30-12-2019 15:30 |
30-12-2019 | 1935 | Normal | 30-12-2019 06:30 | 30-12-2019 09:00 |
I would like to count the Sick days, not the sick shifts, it that makes sense.. The desired result for this would be 1.
I've tried using DISTINCTCOUNT but this gives me the number of different employees that has been sick during the chosen period.
How should I proceed?
Thanks in advance!
Solved! Go to Solution.
hi @setis
try a calculated table
Table = summarize(FILTER('Table';'Table'[Timesheet]="illness");'Table'[Salary ID];"illnessdays";distinctcount('Table'[WorkDay]))
do not hesitate to give a kudo to useful posts and mark solutions as solution
@setis ,
You can simply create a Measure like Below:
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
@Tahreem24 Thanks for your answer.
I don't think that this is what I'm looking for.
If you add a new line with a different SalaryID and Timesheet = "Illness" the result would still be 1 and my desired result would be 2. Because there are 2 workdays labeled as "Illness"
@setis ,
Instead of DISTINCTCOUNT use COUNT function. Refer below screen shot:
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
@setis ,
Use the below measure instead:
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
@Tahreem24 , thanks but the problem with this is that the first to lines in my example would be counted as 2 days and not one
hi @setis
try a calculated table
Table = summarize(FILTER('Table';'Table'[Timesheet]="illness");'Table'[Salary ID];"illnessdays";distinctcount('Table'[WorkDay]))
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 This works, thanks, Would it be possible to obtain the same result with a measure instead?