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.
Hello!
I'm new to PBI and DAX and am struggling with this problem. Please see attached .pbox with sample data
I have to tables, Break Schedule and Down Time.
I would like to add a column to Down Time that displays Yes when a Down Time is likely a break.
Here is the logic that I'm trying to implement to determine if a down time is a break:
1. The down time duration must be longer than the break duration
2. The down time has to start within 1 hour of the scheduled break start time.
Using this logic, I would expect Row 2 and Row 5 to be marked as a break, as seen below:
I have a vague idea of what to do, but don't have a clue how to go about it in PBI/DAX.
Any guidance on this will be greatly appreciated!!!
Thanks,
Sidd
Solved! Go to Solution.
Hi Sidd,
You can try a calculated column like this.
Column = VAR breaktime1 = CALCULATE ( VALUES ( 'Break Schedule'[Break Start] ), 'Break Schedule'[Index] = 1 ) VAR breakduration1 = CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 1 ) VAR breaktime2 = CALCULATE ( VALUES ( 'Break Schedule'[Break Start] ), 'Break Schedule'[Index] = 2 ) VAR breakduration2 = CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 2 ) VAR breaktime3 = CALCULATE ( VALUES ( 'Break Schedule'[Break Start] ), 'Break Schedule'[Index] = 3 ) VAR breakduration3 = CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 3 ) RETURN IF ( 'Down Time'[Duration] <= MIN ( 'Break Schedule'[Duration] ), "No", IF ( ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime1, MINUTE ) ) <= 60 && [Duration] > breakduration1, "Yes", IF ( ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime2, MINUTE ) ) <= 60 && [Duration] > breakduration2, "Yes", IF ( ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime3, MINUTE ) ) <= 60 && [Duration] > breakduration3, "Yes", "No" ) ) ) )
I added a test row to verify the formula above.
Best Regards,
Dale
Hi Sidd,
You can try a calculated column like this.
Column = VAR breaktime1 = CALCULATE ( VALUES ( 'Break Schedule'[Break Start] ), 'Break Schedule'[Index] = 1 ) VAR breakduration1 = CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 1 ) VAR breaktime2 = CALCULATE ( VALUES ( 'Break Schedule'[Break Start] ), 'Break Schedule'[Index] = 2 ) VAR breakduration2 = CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 2 ) VAR breaktime3 = CALCULATE ( VALUES ( 'Break Schedule'[Break Start] ), 'Break Schedule'[Index] = 3 ) VAR breakduration3 = CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 3 ) RETURN IF ( 'Down Time'[Duration] <= MIN ( 'Break Schedule'[Duration] ), "No", IF ( ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime1, MINUTE ) ) <= 60 && [Duration] > breakduration1, "Yes", IF ( ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime2, MINUTE ) ) <= 60 && [Duration] > breakduration2, "Yes", IF ( ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime3, MINUTE ) ) <= 60 && [Duration] > breakduration3, "Yes", "No" ) ) ) )
I added a test row to verify the formula above.
Best Regards,
Dale
Thanks Dale! Your solution was taught me how to use PowerBI a little better. Cheers!
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |