Selecting Start Shift and End Shift Using Slicer

[Jarrod]

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.

[Jarrod]

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.

Cheers,

Jarrod

[amitchandak]

@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

[Jarrod]

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.

[amitchandak]

@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

[Jarrod]

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]

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

Will that solve purpose?