Forum Discussion
Dax measures , multiple if's
1. I need a dax measure that will return the items in the blue based on the duration and first half, if first half is PR, the measure should return 12, otherwise, it should return what is in first half column. In this measure, day 6 and 7 should return blank.
2. for the second measure, 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
expected output
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
6 Replies
Hi Anonymous
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.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 ColumnCalcColumn_FirstHalf = IF ( 'Shift'[Status] = "PR", "12", 'Shift'[Status] )
and this Query as Calculated MeasureMeasure_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
