Custom slier values that filter/slice data source table using custom contidions

[gsrikar33]

Hello Team,

Requirement - To create a slicer that will slice the table visual based on date/time columns. The slicer should have custom values.

shift 1 - should filter all the values for today between 00-23:59 UTC

shift 2 - should filter all values for today between 22-23:59 UTC

              and tomorrow between 00-10 UTC

Work Done:

I initially tried to do this in the SQL query(used to import the data source) itself by creating an extra column 'Shift' and assigning values [shift1,shift2] based on conditions.

Problem with this approach is, there is overlaping. Meaning, some records has to appear in both shifts.

So, I need to get this done in PBI itself.

Below is the sample of PBi design that I'm trying to achieve.

I am thinking of creating CALCULATEDTABLEs for each shift and use bookmarks to show the desired table upon selected slicer option. But this is slightly clumsy.

Is there a simple was to achieve this?

[v-linyulu-msft · Accepted solution]

Hi, @gsrikar33 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.You need to create the following calculated table and use it as a slicer, ensuring that the slicer does not directly affect the original data. 

ShiftSelection = DATATABLE("Shift", STRING, {{"Shift 1"}, {"Shift 2"}})

3. Below are the measure I've created for your needs:

Shift1Filter = 
VAR SELECT1 =
    SELECTEDVALUE ( 'ShiftSelection'[Shift] )
VAR SHIFT1 =
    IF (
        MAX ( 'Table'[Due Date] ) >= TODAY ()
            && MAX ( 'Table'[ETD Date] )
                < TODAY () + 1,
        1,
        0
    )
VAR SHIFT2 =
    IF (
        MAX ( 'Table'[Due Date] ) = TODAY ()
            && MAX ( 'Table'[Due Time (Z)] ) >= TIME ( 22, 0, 0 )
            && (
                MAX ( 'Table'[ETD Date] ) = TODAY ()
                    || MAX ( 'Table'[ETD Date] )
                        = TODAY () + 1
                        && MAX ( 'Table'[ETD Time (Z)] ) <= TIME ( 10, 0, 0 )
            ),
        1,
        0
    )
RETURN
    IF (
        ISFILTERED ( 'ShiftSelection'[Shift] ),
        IF ( SELECT1 = "Shift 1", SHIFT1, SHIFT2 ),
        1
    )

4.Apply measure to visual object filters.

5.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gsrikar33]

You made my day!

Thanks a ton for the solution.