Working Days per Employee

[deboec]

Hi,

I have a table that shows records for every activity of employees [Activity]:

ID	Activity_ID	Employee_ID	Date	Duration	Completed
1	1	90	2021/09/01	0	1
2	1	91	2021/09/01	0	1
3	1	91	2021/09/02	0	1
4	2	90	2021/09/01	1	0
5	3	92	2021/09/03	1	0
6	1	90	2021/09/03	0	1
7	1	92	2021/09/02	0	1
8	1	93	2021/09/01	0	0

I need a measure that calculates the days an employee [Employee_ID] worked, meaning if an employee has a record in the [Activity] table on any given date it should count as 1.

Another problem is that an employee can create an activity before the actual day and has to check a box after the activity so it is shown in the table as [Completed] = 1. This on the other hand only occurs if the [Activity_ID] = 1. If [Activity_ID] is 2 or 3 [Completed] will always be 0 but [Duration] will either be 1 or 0.5 (for full day or half day).

I created measures for 3 different Activities [Activity_ID], e.g. for [Activity_ID] =1

Same for [Activity_ID] =2 and [Activity_ID] =3.

For the total days I would then create another measure with an Addition of the 3 different measures.

If I display this in a matrix visual however, I get the right Days per Employee but the total is of course also the DISTINCTCOUNT() of the days in the table (matrix for example table above):

Employee_ID	Days Activity1
90	2
91	2
92	2
Total	3 (for the 3 distinct dates in the table with an activity)

What I want displayed is the sum as the Total in the matrix.

Thank you for your help

[v-shex-msft · Accepted solution]

Hi @deboec,

You can add the 'activity id' field to the table visual and use the below measure formula to calculate workdays of each activity and grouped by employee:

Days Activity =
CALCULATE (
    DISTINCTCOUNT ( Activity[Date] ),
    FILTER ( ALLSELECTED ( Activity ), Activity[Completed] = 1 ),
    VALUES ( Activity[Activity_ID] )
)

Regards,

Xiaoxin Sheng

[deboec]

Forgot to post the measure für Working Days for Activity1, but here it is:

Days Activity1 =
CALCULATE(
          DISTINCTCOUNT(
                    Activity[Date]
          ),
          Activity[Activity_ID] = 1,
          Activity[Completed] = 1
)