Forum Discussion

H_Jones's avatar
H_Jones
Helper I
10 years ago
Solved

Overlapping Slicer

Hello PBI Community...   I have a question that I don’t even know is possible.   I have a report in PBI desktop and I want there to be buttons (potentially a slicer) to show the income either for...
  • Beckham's avatar
    Beckham
    10 years ago

    I'd do a disconnected table/switch combination that looks like this:

     

    Step #1 Create a disconnected table:

    Number    Chooser   

    0               YTD

    1               Last Week

     

    Step 2 the measure

    = Switch(min(DisconnectedTable[Number]),

    0,[Calculation #1],

    1,[Calculation #2])

     

    Step 3. Bring the Chooser column, from the disconnected table, on to the pivot as a slicer. When you select YTD, the min will filter to 0, and the switch will run calc #1. When you select Last Week, the min will filter to 1, and the switch will run calc#2

     

    Essentially you make two distinct calcs, one that does YTD, and another that does last week. Then the switch will toggle between them. 

     

    Does this help?

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

H_Jones

 

In this scenario, you can first create two measures for YTD and Last Week. Then create another new table for these two measures. So that either of the measures can be selected in the slicer and you can get the expected result.

 

Please refer to following steps:

  1. Create two measures for YTD and Last Week. I assume week begins on Sunday (1) and ends on Saturday (7) here.
    YTD_Income = 
CALCULATE ( SUM ( Table1[Income] ), DATESYTD ( Table1[Date] ) )

    LastWeek_Income = 
VAR LastDay = LASTDATE ( Table1[Date] )
VAR WeekDayNum = WEEKDAY ( LastDay )
RETURN
    (
        CALCULATE (
            SUM ( Table1[Income] ),
            FILTER (
                ALL ( Table1[Date] ),
                Table1[Date] > LastDay - 7 - WeekDayNum
                    && Table1[Date] <= LastDay - WeekDayNum
            )
        )
)
  2. Create a new table for above two measures.
  3. Drag a slicer into your canvas and put “Measure” column into Field.

Regards,