Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi I need help with a function to create shifts of specific duration in days and time. I have Day and time data in two columns as shoun in Picture below. It would be great help please.
Solved! Go to Solution.
Try this:
Shift =
VAR _D = [Day]
VAR _T = [Time ]
RETURN
SWITCH (
TRUE (),
_D
IN { "Sunday", "Monday", "Tuesday", "Wednesday" }
&& _T >= TIMEVALUE ( TIME ( 7, 15, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 17, 45, 00 ) ), "Shift A",
_D
IN { "Sunday", "Monday", "Tuesday", "Wednesday" }
&& _T > TIMEVALUE ( TIME ( 00, 00, 01 ) )
&& _T <= TIMEVALUE ( TIME ( 14, 45, 00 ) ), "Shift B",
_D
IN { "Sunday", "Monday", "Tuesday", "Wednesday" }
&& _T >= TIMEVALUE ( TIME ( 18, 45, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 23, 59, 59 ) ), "Shift B",
_D
IN { "Thursday", "Friday", "Saturday" }
&& _T >= TIMEVALUE ( TIME ( 7, 15, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 17, 45, 00 ) ), "Shift C",
_D
IN { "Thursday", "Friday", "Saturday" }
&& _T > TIMEVALUE ( TIME ( 00, 00, 01 ) )
&& _T <= TIMEVALUE ( TIME ( 14, 45, 00 ) ), "Shift D",
_D
IN { "Thursday", "Friday", "Saturday" }
&& _T >= TIMEVALUE ( TIME ( 18, 45, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 23, 59, 59 ) ), "Shift D"
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Can you post sample data as text and expected output?
Not enough information to go on;
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.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Hi Thank you for your time to respond. I have data of day and time in same data table with no other table relation. Day is in text format and time is in time format. I want to combine day and time to make 4 diffetent shifts (shift A, shift B, shift C and Shift D) time and days of shifts are specified in my first message. Thanks for your help. I want to see shift wise performance.
Day | Time | Shift (Required Out Put) |
Wednesday | 2:15 AM | Shift A |
Thursday | 4:12 AM | ? |
Friday | 3:46 PM | ? |
Saturday | 7:24 PM | ? |
Sunday | 2:13 AM | ? |
Monday | 6:12 PM | ? |
Try this:
Shift =
VAR _D = [Day]
VAR _T = [Time ]
RETURN
SWITCH (
TRUE (),
_D
IN { "Sunday", "Monday", "Tuesday", "Wednesday" }
&& _T >= TIMEVALUE ( TIME ( 7, 15, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 17, 45, 00 ) ), "Shift A",
_D
IN { "Sunday", "Monday", "Tuesday", "Wednesday" }
&& _T > TIMEVALUE ( TIME ( 00, 00, 01 ) )
&& _T <= TIMEVALUE ( TIME ( 14, 45, 00 ) ), "Shift B",
_D
IN { "Sunday", "Monday", "Tuesday", "Wednesday" }
&& _T >= TIMEVALUE ( TIME ( 18, 45, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 23, 59, 59 ) ), "Shift B",
_D
IN { "Thursday", "Friday", "Saturday" }
&& _T >= TIMEVALUE ( TIME ( 7, 15, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 17, 45, 00 ) ), "Shift C",
_D
IN { "Thursday", "Friday", "Saturday" }
&& _T > TIMEVALUE ( TIME ( 00, 00, 01 ) )
&& _T <= TIMEVALUE ( TIME ( 14, 45, 00 ) ), "Shift D",
_D
IN { "Thursday", "Friday", "Saturday" }
&& _T >= TIMEVALUE ( TIME ( 18, 45, 00 ) )
&& _T <= TIMEVALUE ( TIME ( 23, 59, 59 ) ), "Shift D"
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
This is not the solution to my Problem. Can somebody give me the exact DAX to use in the new Shift Column?
User | Count |
---|---|
69 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
25 |