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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors