Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would really appreciate some help please. I have Rostering table with Employee, Date and Work Type. Work Type, among others, can be Work, Overtime, Sick or Rostered off. I am trying to calulate for each employee, a count of the number of instances an employee takes a sick day in the day after overtime and a separate measure for 2 days after etc. It is possible for an employee to have Work and Overtime on the same day. The data looks as follows:
Employee | Date | Work Type |
Employee1 | 1/02/2019 | Work |
Employee1 | 2/02/2019 | Work |
Employee1 | 2/02/2019 | Overtime |
Employee1 | 3/02/2019 | Sick |
Employee1 | 4/02/2019 | Sick |
Employee1 | 5/02/2019 | Work |
Employee1 | 5/02/2019 | Rostered off |
Employee2 | 1/02/2019 | Work |
Employee2 | 2/02/2019 | Overtime |
Employee2 | 3/02/2019 | Work |
Employee2 | 4/02/2019 | Sick |
Employee2 | 5/02/2019 | Work |
Output would be as follows:
Employee | Sick occur after 1 day |
Employee1 | 1.00 |
Employee2 | - |
Employee | Sick occur after 2 days |
Employee1 | 1.00 |
Employee2 | 1.00 |
Can someone assist with a DAX solution please? I just can't seem to get it working.
cheers
Solved! Go to Solution.
Hi @vanschex
You may create two columns and two measures to get it.For example:
Next1day_Worktype = CALCULATE ( MAX ( Table1[Work Type] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = EARLIER ( Table1[Date] ) + 1 ) )
Next2day_Worktype = CALCULATE(MAX(Table1[Work Type]),FILTER(ALLEXCEPT(Table1,Table1[Employee]),Table1[Date]=EARLIER(Table1[Date])+2 ))
Measures:
Sick occur after 1 day = CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next1day_Worktype]="Sick")))
Sick occur after 2 day = CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next2day_Worktype]="Sick")))
Regards,
Hi @vanschex
You may create 2 measures like below:
Sick occur after 1 day = VAR overtime_date = CALCULATE ( MIN ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Work Type] = "Overtime" ) ) RETURN CALCULATE ( COUNT ( Table1[Employee] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = overtime_date + 1 && Table1[Work Type] = "Sick" ) )
Sick occur after 2 day = VAR overtime_date = CALCULATE ( MIN ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Work Type] = "Overtime" ) ) RETURN CALCULATE ( COUNT ( Table1[Employee] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = overtime_date + 2 && Table1[Work Type] = "Sick" ) )
Regards,
Hi Cherie,
Thanks so much for the reply. I have implemented the code as supplied but unfortunately it is only calculating a value of 1 per employee (I have examples where the occurrence is > 1) and ommitted at least 1 employee that I can see. Are you able to assist please?
cheers
Emil
Hi @vanschex
Could you update the sample data to show me the difference(where the occurrence is > 1)?I cannot reproduce your scenario.Maybe you may try to use DISTINCTCOUNT as below.
Sick occur after 1 day = VAR overtime_date = CALCULATE ( MIN ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Work Type] = "Overtime" ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Table1[Employee] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = overtime_date + 1 && Table1[Work Type] = "Sick" ) )
Regards,
Thanks again. The following test file replicates the issue:
Employee | Date | Work Type |
Employee1 | 1/02/2019 | Work |
Employee1 | 2/02/2019 | Work |
Employee1 | 2/02/2019 | Overtime |
Employee1 | 3/02/2019 | Sick |
Employee1 | 4/02/2019 | Sick |
Employee1 | 5/02/2019 | Work |
Employee1 | 5/02/2019 | Rostered off |
Employee2 | 1/02/2019 | Work |
Employee2 | 2/02/2019 | Overtime |
Employee2 | 3/02/2019 | Work |
Employee2 | 4/02/2019 | Sick |
Employee2 | 5/02/2019 | Work |
Employee1 | 6/02/2019 | Work |
Employee1 | 7/02/2019 | Overtime |
Employee1 | 8/02/2019 | Sick |
Employee1 | 9/02/2019 | Sick |
Employee1 | 10/02/2019 | Work |
Employee1 | 11/02/2019 | Rostered off |
employee3 | 29/06/2018 | Overtime |
employee3 | 30/06/2018 | Overtime |
employee3 | 1/07/2018 | Sick |
employee3 | 2/07/2018 | Sick |
Employee 1 should have a cont of 2. Employee 3 is not reported at all
Hi @vanschex
You may create two columns and two measures to get it.For example:
Next1day_Worktype = CALCULATE ( MAX ( Table1[Work Type] ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee] ), Table1[Date] = EARLIER ( Table1[Date] ) + 1 ) )
Next2day_Worktype = CALCULATE(MAX(Table1[Work Type]),FILTER(ALLEXCEPT(Table1,Table1[Employee]),Table1[Date]=EARLIER(Table1[Date])+2 ))
Measures:
Sick occur after 1 day = CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next1day_Worktype]="Sick")))
Sick occur after 2 day = CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next2day_Worktype]="Sick")))
Regards,
I really appreciate your assistance on this! It works as intended
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |