Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Control a Measure using Multi Selction Disconnected Slicer

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

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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