Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Using slicer to filter before appending two queries

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.