Two Slicers - to pass filters to a line chart

[shaunwilks]

I am developing a Comparison Page.

The idea is to compare two of the same code - lets call it an "Item".

The idea is to have two slicers -

1) One on the left side of the page to select one Item, and

2) The second slicer on the right side of the page

Using edit interactions some KPI and Cards sit under each filter and work well to show only the details for the item selected above it. So clearly left side of the page would be compared with the right side of the page. This mostly works well because the cards and the tables etc all filter for the one specific item.

However the problem I am having is trying to have both slicers filter the same chart at the bottom of the page.

The line chart was to show a line per item with sales over time. The Item becomes the "Legend" of the chart.

Using Edit interactions I set it up so both slicer relate to the line chart.

Select the item in the first slicer --> Tick The line chart displays a line for the selected item.

Select the item in the second slicer --> No Good - The line chart goes blank and showws nothing.

The strange thing is if I configure the first slicer for select multiple and I tick two items in the same slicer - the chart shows correctly. A line for every item I select.  But this is not going to help me with the fact I have tables and KPI and cards that are to show for the second item that wont show correctly.

How do I have the two slicers to select the same "Field" in two different slicers and have it apply to 1 chart ?

I attached an image that should help you understand what I am attempting.

[v-yuezhe-msft]

@shaunwilks,

In your scenario, when you select an item in the first slicer and select a different item in the second slicer, it is a "And" relationship but not "or" relationship, Power BI will check if there is a customer name called "item 1 & Item2" and the line chart will return blank when there is no such customer name.

In your scenario, make the line chart not filtered by the two slicers, then use visual level filters to filter the line chart.

Regards,
Lydia

[shaunwilks]

Thats not really desirable as you are forcing the user to select the two compare items in the slicers in the page and then again in the Visual level filter.

I then tried having two seperate non related tables to populate the slicers 1 and 2.

I used measures to check the contents of each filter. 

I had then hoped I could set the measures that contained the slicer text as Visual level filters. 

But of Course I cant drag Measures into the Visual level filters unless using the Top N filter. 

Otherwise I would have been onto a winner.

There are alot of genius' out there and I am sure they can suggest a way that allows two slicers to populate the line chart at the bottom of the page. 

[shaunwilks · Accepted solution]

Ok so I have an answer I resolved myself

1 ) I created two new tables "Compare Table 1", "Compare Table 2"

They have 1 field in each table and are simply a list of all Items for comparison.

I ensure there are no table joins to other tables.

2) I created four new measures

i) SelectedCompare1 = if(HASONEVALUE('Item Compare1'[Name]),ALLSELECTED('Item Compare1'[Name]),"")

 This simply gets the value of slicer1 into measure

ii) SelectedCompare2 = if(HASONEVALUE('Item Compare2'[Name]),ALLSELECTED('Item Compare2'[Name]),"")

This simply gets the value of slicer2 into a measure

ii) CompareSalesItem = if(HASONEVALUE('Items'[Name]),ALLSELECTED('Items'[Name]),"")

This simply gets each row values Item Name into a measure

iv) CompareFlag = If(OR([SelectedCompare1]=[CompareSalesItem],[SelectedCompare2]=[CompareSalesItem]),"Yes","No")

This sets a flag as a measure for each row of the sales table as a Yes or No in the instance the Item equalled one of items in either of the two slicers.

3) The Line Chart

I drag the measuer CompareFlag into the Visual filters of the chart and set its filter value = Yes.

As these two items are those specified for compare the chart only shows the two items in the selected slicers.

[leoco91]

Hi shaunwilks,

could you share the .pbix file? I've tried to follow your solution but I can't obtain your result. I've tried to change relationship between tables, the slicers interaction but nothing.

Thank you very much,

Leonardo

[shaunwilks]

Sorry but the pbix has some sensitive elements to it that I cannot share specifically.

If you hadnt already checkout the new release of PowerBI in May, June and July.

I have read and seen a demo of split slicers in a new enhancement that would allow it to be done in a much easier way than I was forced to above. I never liked seperating the selections into two tables but it was my only option given the functionality available at the time.

Hopefully teh new enhancement is what you need as the video showed it even used on a comparison page.