cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarlonK
Frequent Visitor

Create time slicer for multiple overlapping shifts

Hello, 

 

I have a Date/Time column in my data source, and I have written a calculated column with 3 IF statements to break up the data into three shifts, basically IF [Date/Time] >= 00:00 and [Date/Time] < 08:00 THEN "00:00 - 08:00" and so on for the three shifts of the day, so that I can add the field to a slicer and filter on these three shifts:

 

00:00 - 0800

08:00 - 1600

16:00 - 00:00

 

This part works great, but the issue is that different users use different shifts depending on their location, so I'd like to have some additional shift options to slice by such as:

 

23:00 - 07:00

07:00 - 15:00

15:00 - 23:00

 

Since the 3 IF statements I already wrote alread account for all the data, writing 3 more IF statements for the additional shifts I want to filter by won't do a thing... so I'm wondering, is there a way to group these so that I can filter my Date/Time field by any of the 6 shifts above?

 

In Tableau, I would create a string parameter with these 6 options and then write a calculated field to filter the data appropriately based on the selected shift option in the string parameter, but I'm not sure how to accomplish this in Power BI. 

 

Thank you!

1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
Solution Specialist

Create a new table for the shifts definitions (per hour) and use it in a many to many with single filter direction relathionship.

m_refaei_0-1627679718174.pngm_refaei_1-1627679733104.png

 

Please check this sample file.

View solution in original post

4 REPLIES 4
Mohammad_Refaei
Solution Specialist
Solution Specialist

Create a new table for the shifts definitions (per hour) and use it in a many to many with single filter direction relathionship.

m_refaei_0-1627679718174.pngm_refaei_1-1627679733104.png

 

Please check this sample file.

View solution in original post

Brilliant! This is an excellent and simple solution.

 

Thank you!

My pleasure

JesusYaya
Frequent Visitor

Hi @MarlonK ,

In that case, is the a reason why you wouldn't consider a Slicer of the hour as a Between presentation? So the users can manually put the hour range that they want to visualize:

JesusYaya_1-1627679725889.png

Jesus.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!