Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarlonK
Helper I
Helper I

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.

Brilliant! This is an excellent and simple solution.

 

Thank you!

My pleasure

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.