Create time slicer for multiple overlapping shifts

[MarlonK]

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!

[Mohammad_Refaei · Accepted solution]

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

Please check this sample file.

[MarlonK]

Brilliant! This is an excellent and simple solution.

Thank you!

[Mohammad_Refaei]

My pleasure

[Anonymous]

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:

Jesus.