Forum Discussion
Dax measures , multiple if's
Hi @Selded ,
To solve this logically, you would first group the records by employee and then sort each employee’s records by date so the days are processed in the correct order. As you go through each record, maintain a counter that tracks consecutive working days. If the status is PR, check whether the previous record was also PR and on the day immediately before if it is, increment the counter and if not reset it to one since a new streak begins. If the status is anything other than PR reset the counter to zero because the streak is broken. After updating the counter for each day apply the business rule: if the counter is exactly the 6th or 7th consecutive PR day, override the shift duration with 12, otherwise keep the shift duration as it is in the data. This process continues across all dates for each employee independently, with the counter resetting whenever there’s a break in presence.
Thank you
Hello Greg,
1. Sample data as shown in table below
2. Calculated column, if firsthalf=PR, return 12, otherwise return first half
3. Calculated measure to return same as calculated column.
4. i want a dax that will count the number of days John Doe is at work contineously, and for day 6 and 7 return 12. It should aonly return the 12 for day 6 and 7
Below is the data sample
| UserID | PDate | UserName | Status | WorkingShift | Shift Duration | Calculated colum |
| TT2S027 | 17/09/2024 0:00 | JESSY MATT | PR | RD | 12 | 12 |
| TT2S027 | 18/09/2024 0:00 | JESSY MATT | AL | RD | 0 | RD0 |
| TT2S027 | 19/09/2024 0:00 | JESSY MATT | PR | DS | 0 | AL |
| TT2S028 | 20/09/2024 0:00 | JESSY MATT | ML | DS | 0 | ML |
| TT2S026 | 18/09/2024 0:00 | LUKE KATE | ML | NS | 0 | ML |
| TT2S028 | 19/09/2024 0:00 | ASHLEY MOORE | ML | NS | 0 | ML |
| TT2S029 | 20/09/2024 0:00 | MARK COOK | ML | NS | 0 | ML |
| TT2S028 | 17/09/2024 0:00 | JOHN DOE | PR | DS | 12 | 12 |
| TT2S028 | 18/09/2024 0:00 | JOHN DOE | PR | DS | 12 | 12 |
| TT2S028 | 19/09/2024 0:00 | JOHN DOE | PR | DS | 12 | 12 |
| TT2S028 | 20/09/2024 0:00 | JOHN DOE | PR | DS | 12 | 12 |
| TT2S028 | 21/09/2024 0:00 | JOHN DOE | PR | NS | 12 | 12 |
| TT2S028 | 22/09/2024 0:00 | JOHN DOE | PR | NS | 12 | 12 |
| TT2S028 | 23/09/2024 0:00 | JOHN DOE | PR | NS | 12 | 12 |
| TT2S028 | 24/09/2024 0:00 | JOHN DOE | PR | NS | 12 | 12 |
| TT2S028 | 25/09/2024 0:00 | JOHN DOE | RD | RD | 0 | RD |
| TT2S028 | 26/09/2024 0:00 | JOHN DOE | RD | RD | 0 | RD |
| TT2S028 | 27/09/2024 0:00 | JOHN DOE | RD | RD | 0 | RD |
| TT2S028 | 28/09/2024 0:00 | JOHN DOE | RD | RD | 0 | RD |
| TT2S028 | 29/09/2024 0:00 | JOHN DOE | PR | DS | 12 | 12 |
| TT2S028 | 30/09/2024 0:00 | JOHN DOE | AL | DS | 0 | AL |
| TT2S028 | 01/10/2024 0:00 | JOHN DOE | AL | DS | 0 | AL |
| TT2S028 | 02/10/2024 0:00 | JOHN DOE | AL | DS | 0 | AL |
| TT2S028 | 03/10/2024 0:00 | JOHN DOE | AL | DS | 0 | AL |
| 12 |
Hi Anonymous ,
Thanks for reaching out to the Microsoft fabric community forum.
To get your desired output use this DAX query as Calculated Column
CalcColumn_FirstHalf =
IF (
'Shift'[Status] = "PR",
"12",
'Shift'[Status]
)
and this Query as Calculated Measure
Measure_FirstHalf_Text =
VAR CurrentStatus =
MAX ( 'Shift'[Status] )
RETURN
IF (
CurrentStatus = "PR",
"12",
CurrentStatus
)
keep in mind that IF condition can return the same data type for true and false condition, that why in our scenario we are converting 12 into a string.
Attaching the .pbix file for reference.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
This works, thank you. are you able to assit with the 4th question?
Hi @Selded ,
To solve this logically, you would first group the records by employee and then sort each employee’s records by date so the days are processed in the correct order. As you go through each record, maintain a counter that tracks consecutive working days. If the status is PR, check whether the previous record was also PR and on the day immediately before if it is, increment the counter and if not reset it to one since a new streak begins. If the status is anything other than PR reset the counter to zero because the streak is broken. After updating the counter for each day apply the business rule: if the counter is exactly the 6th or 7th consecutive PR day, override the shift duration with 12, otherwise keep the shift duration as it is in the data. This process continues across all dates for each employee independently, with the counter resetting whenever there’s a break in presence.
Thank you