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.
I have a table(A) that has different time for each event_id. And also I have a shift table(B) that has different shift times.
I want to check the time in table (A) with [Start time] and [EndTime] in shfit table[B} and determine the shiftID.
In this case I can have a slicer that connect to table B and can create a relationship between table A and B with ShiftID.
Table A: Table B(shift table)
1 6:30 AM 6:00 AM - 2:00 PM morning
2 5:30 PM 2:00 PM - 11 PM Evening
3 4:00 AM 11:00 PM- 6:00 AM Night
How can determing the 6:30 is shift morning?
Thank you in advance.
Solved! Go to Solution.
In Power Query (M) it can be done with an approximate lookup. In general this works fastest if the lookup table is converted to a buffered list.
This is Table B:
Notice the buffered list, consist of 3 items, each with 3 nested items {0) = Time From, {1} = Time To and {2} = Shift.
The last item is selected with Time From less than or equal to the time in Table A; if no time is less than or equal to the time in Table A (< 6:00 AM), then the second parameter of List.Last, default value List.Last(BufferdListB), kicks in.
let
Source = #"Table A",
BufferedListB = List.Buffer(Table.ToRows(#"Table B")),
AddedShift = Table.AddColumn(Source, "Shift", (earlier) => List.Last(List.Select(BufferedListB, each _{0} <= earlier[Time]),List.Last(BufferedListB)){2}, type text)
in
AddedShift
Hi @Nicki,
Based on my test, you should be able to use the formula below to create a new calculate column in table(A) to get the corresponding shiftID in your scenario.
Column = IF ( Table1[Time] > TIMEVALUE ( "6:00 AM" ) && Table1[Time] <= TIMEVALUE ( "2:00 PM" ), "Morning", IF ( Table1[Time] > TIMEVALUE ( "2:00 PM" ) && Table1[Time] <= TIMEVALUE ( "11:00 PM" ), "Evening", IF ( Table1[Time] > TIMEVALUE ( "11:00 PM" ) || Table1[Time] <= TIMEVALUE ( "6:00 AM" ), "Night" ) ) )
Regards
Thank you for responds.
It works fine in DAX. If I want to apply this soultion in M how it can be.
If we create a calculate column in DAX we cannot create a relationship between two table to filter data based on slicer selection.
In Power Query (M) it can be done with an approximate lookup. In general this works fastest if the lookup table is converted to a buffered list.
This is Table B:
Notice the buffered list, consist of 3 items, each with 3 nested items {0) = Time From, {1} = Time To and {2} = Shift.
The last item is selected with Time From less than or equal to the time in Table A; if no time is less than or equal to the time in Table A (< 6:00 AM), then the second parameter of List.Last, default value List.Last(BufferdListB), kicks in.
let
Source = #"Table A",
BufferedListB = List.Buffer(Table.ToRows(#"Table B")),
AddedShift = Table.AddColumn(Source, "Shift", (earlier) => List.Last(List.Select(BufferedListB, each _{0} <= earlier[Time]),List.Last(BufferedListB)){2}, type text)
in
AddedShift
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |