Control a Measure using Multi Selction Disconnected Slicer

[Anonymous]

Hi

I'm trying to understand how to do the above.

What I want to achieve is best exampled by the following:

So I want to choose multiple selections on the blue slicer to affect the blue line, and multiple selections on the yellow slicer to affect the yellow line. I have this working for single selections.

Here is a link to a less complex example pbix file, based on fruits, and a line chart with just one line and slicer - if I can get that working properly, I can achieve the above.

Example.PBIX

This is an example of what it should look like, but with three normal slicers - it shows a total of 5 fruits which are Hard and from France, which is correct:

The problem is these slicers affect the visual, not the measure, which means if I plot a second measure on the same visual, it will be affected by the same selections. I want a second measure to be affected by a different slicer and set of selections.

So I have, with help from others on here, developed a disconnected slicer based on a slicer table with all of the values in that I might want to slice on, and a duplicate table with the features I want to slice on unpivoted, which gives me this set up, using the following measure:

Line 1 = CALCULATE([Number of Rows],FILTER(Duplicate,Duplicate[Value] in values(Slicer[Value])))

I've added a matrix visual in to show what is happening - it is basically acting a bit like an or - i.e. giving me all fruits that are either hard or from France (but only counting the ones that are both once). What I want it to plot is just the 5 that is highlighted at the intersection between France and Hard. As I say, easy to achieve with multiple slicers affecting a visual, but I want the slicer to affect the measure, not the visual. This does but in the wrong way.

Sorry for the long post, but I wanted to ensure that I communicated the problem effectively.

Many Thanks

Ian

[v-cherch-msft]

Hi @Anonymous 

It seems you may create two slicer tables and then create the measure like below.Please check Page1 in attached file.

Measure =
CALCULATE (
    [Number of Rows],
    FILTER (
        Fruits,
        Fruits[Country] IN VALUES ( Country_Slicer[Country] )
            && Fruits[Feature] IN VALUES ( Feature_Slicer[Feature] )
    )
)

Regards,

[Anonymous · Accepted solution]

Thanks, @v-cherch-msft  - that would have worked I think, but I have multiple features I want to slice on, and this would involve creating a slicer table for each feature, which would have got hard to maintain.

The solution, in the end, came from my boss who had a play and worked out it was best done by creating two datasets in Power Query from one, and then having a measure in each dataset that is plotted on the visual. That way you don't need any slicer tables - you slice on the values in each dataset - I've tested and it works.

The link is here to the solution - Final Solution

Thanks again