Forum Discussion

SritejaGolla's avatar
SritejaGolla
Icon for Microsoft Employee rankMicrosoft Employee
2 years ago
Solved

Two slicers (from same column) and one visual show excluded values.

Hello Everyone,
I have the below request and i was able to find the solution when i was using only one slicer from the below article (https://youtu.be/1SnELZGveYs?si=hCBTqIDxEq8UEwDk) 

and could not able to resiprocate when i have to use two slicers for a visual.

Here is the problem statement with sample data but in real its a huge volume:

Two Slicers (both contains "Country" Column) and a Matrix visual(it contains Flags and Country in rows as hierarchy).

 

CountryFlags
IndiaYellow
AustraliaBlue
USA 
Africa 

 

Steps1 : when None of the slicers are selected with any values, then the Matrix displays data as below in hierarchy way as below on rows section (excluding other data as its not the point of concern).

Yellow
          India
Blue
         India
                   Australia
        USA
        Africa

 

Steps 2: when i select "India" from first slicer then the output is as below which is working as expected.

Yellow
          India
Blue
         India

 

Step 3: when i select other than india value from second slicer. then it should show up as below (excluded selection).

for example, if i select "Africa" since Africa is not part of Yellow and it should show only Yellow.

Yellow
          India

 

I followed the article mentioned at the top and created a new table with distinct value and a measure for excluded list but i was working fine when it is having only one slicer.

 

looking for any relevant solution for my request.

 

Thanks

Teja

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi SritejaGolla 

     

    Please try this:

    Here I create a measure:

     

    MEASURE =
VAR _Slicer2 =
    SELECTEDVALUE ( SeperateTable[Industry] )
VAR _Vtable =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'TestTable' ), 'TestTable'[Industry] = _Slicer2 ),
        "_Country", [Country]
    )
RETURN
    IF (
        SELECTEDVALUE ( TestTable[Country] ) IN _Vtable,
        BLANK (),
        SELECTEDVALUE ( TestTable[Industry] )
    )

     

    Then add it to the table visual.

    the result is as follow:

     

     

    Best Regards

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

4 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    SritejaGolla can you share the file with sample data or some screenshots of what's not quite working? You should be able to make it work with two slicers just as one, but may need to create another disconnected table for this to work and be deliberate which tables you use in each slicer. Then you'll need to modify the DAX a tiny bit to account for your two slicers / tables.

    • SritejaGolla's avatar
      SritejaGolla
      Icon for Microsoft Employee rankMicrosoft Employee
      2 years ago

      AllisonKennedy , the Data looks like

      ContryIndustry
      IndiaOil
      IndiaPower
      IndiaConstruction
      USPower
      USSoftware
      UAEOil
      UAEFood
      UAEElectronics


      If User selected first slicer "Industry" (which is for selection):
      Input ---> Industry : Power [in slicer]

      Output ---> 

      CountryIndustry
      IndiaPower
      USPower


      and User selects 2nd slicer "Oil" (which needs to exclude the country which has Oil)
      Input ----> Indutry : Oil [in slicer to exclude]

      Output--->

      CountryIndustry
      USPower
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi SritejaGolla 

       

      Please try this:

      Here I create a measure:

       

      MEASURE =
VAR _Slicer2 =
    SELECTEDVALUE ( SeperateTable[Industry] )
VAR _Vtable =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'TestTable' ), 'TestTable'[Industry] = _Slicer2 ),
        "_Country", [Country]
    )
RETURN
    IF (
        SELECTEDVALUE ( TestTable[Country] ) IN _Vtable,
        BLANK (),
        SELECTEDVALUE ( TestTable[Industry] )
    )

       

      Then add it to the table visual.

      the result is as follow:

       

       

      Best Regards

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