Using slicer to filter before appending two queries

[Anonymous]

Hi,

I have the following table that I have appended - current values  and past values which range from 1-12 years into the past, the data is also dived between tenors (3M, 6M etc) which will be the y-axis. Current values have 'Attribute = null, while past values 'Attribute' = 1-11. I would like to keep it as one table for a stacked bar chart visual as I want to use the 'small multiples' function to compare Current vs Past. My issue is that I want to put a slicer on the the 'Past' values so I can compare the current prices to 1year ago, 5 year ago etc. 
I am struggling to figure out how I can use the slicer on 'Attribute' (relates to years) for Past values while making sure the current values don't get filtered out (e.g. I want to compare versures 5 years ago) . Can the data be sliced dynamically before I append the queries of current and past together or is there another way to tacke this?

[Anonymous]

Thank you

[Anonymous · Accepted solution]

Hi @Anonymous 

You can refer to the following solutions.

Sample data 

1.In Power BI Desktop, create a slicer table. the table has no relationship with data table

Slicer = SUMMARIZE(FILTER('Table',[Current/Past]="Past"),[Attribute])

2.Create the following measure

MEASURE =
SWITCH (
    SELECTEDVALUE ( 'Table'[Current/Past] ),
    "Current", CALCULATE ( SUM ( 'Table'[Value] ) ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Attribute] IN VALUES ( 'Slicer'[Attribute] ) )
    )
)

3.Then create a slicer and put the field of the slicer table to the slicer, and create a stacked bar chart table and put the measure and related field to the visual.

Output

Best Regards!

Yolo Zhu

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