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

Selecting Start Shift and End Shift Using Slicer

Hi All,

 

Apologies for hopefully simple question, have only just began getting into Power BI.

 

I have a manufacturing database with items manufactured. These are linked to a date dimension table via a shiftid. Each day has 2 shifts, day & night. Could you please advise how I can select start shift and end shift and filter report to see items manufactured within @startshift and @endshift.

 

In SSRS, this would be simple as you would have a @startshift and @endshift parameter. The value field would be ShiftID and the Text to Display would be ShiftDateName. Is something similar possible in PowerBI using a slicer? This is using on prem so cannot use any custom visuals.

 

Example Manufacturing database

 

Manufactured Records Table

ID | Product ID| ShiftID

1   |2                |2

2   |2                |1

3   |3                |1

4   |3                |1

 

Shift Dimension Table

ShiftID | ShiftDateName            | ShiftYear | ShiftMonth

1          | 20-July-21 Day Shift    | 2021        | 7

2          | 20-July-21 Night Shift | 2021        | 7

 

Thankyou in advance.

6 REPLIES 6
Jarrod
Frequent Visitor

Hi @amitchandak ,

 

When using a slicer on one of the date dimension columns (Example is Full Shift Name as attached), I can only select 1 shift or many shifts. If I want to select a whole month, I need to select 60 shifts (30 x 2 shifts/day). Again, I apologise, I'm sure this is simple but I am not quite sure how to do it. I would like to select a range, for example, 1st Jan 2021 day shift to 31st March 2021 night shift.

 

Jarrod_0-1627363063638.png

 

 

Cheers,

Jarrod

@Jarrod , Best to split this column in power query on the first occurrence of space. And then join date with date table to have month filter and shift filter separately

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak, I will try this. It will still result in more clicks than in SSRS. Maybe this is just a limitation in PowerBI.

 

In SSRS, it as easy as having something like below and query returns all results between @startshift and @endshift.

 

Jarrod_0-1627365264481.png

 

@Jarrod , if shift is date time, between can work. But I doubt any between list of value(slicer) like that.

 

You many need in text format like this 20200101 day shift

20200101 night shift

 

this will ensure that will help from two independent slicer you can work on between

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak 

 

I have a datetime value in date dimension like 2021-01-01 06:00 and 2021-01-01 18:00 but it seems you cannot use the time component in a slicer with "between", only list and dropdown.

 

Thankyou

amitchandak
Super User
Super User

@Jarrod , You can use these two tables on shift id and can use slicer Shift Dimension columns.

Will that solve purpose?

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!