Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Direct and Inverse Selector

Hello,

I have a table like this:

Department     Product     Category

Sports                 AA                00001

Education            BB                00002

Health                 AB                00003

Sports                 AA                00004

Health                 BB                00005

 

I need a slicer for Product that will act as both direct and inverse slicer.

Eg:- If I select Product 'BB' in slicer -> It should display 2 tables.

1st  : Department and Category that HAS product BB

2nd : Department and Category that DOES NOT HAVE product BB

 

Is there a way to acheive this?

 

Thanks

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Right, so create a disconnected table like this:

     

    Table6 = ALL('Table5'[Product])

    Use this for your slicer. Then, create these two measures and use them in your tables. 

     

    Normal Measure = 
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,1,BLANK())


Inverse Measure = 
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,BLANK(),1)

    See Table5, Table6 and Page 3 of attached.

     

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Greg,

       

      Thanks for your response.
      I had a look at this before, it calculates the SUM.

      But, I want to display the contents of the table based on the filter.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        Right, so create a disconnected table like this:

         

        Table6 = ALL('Table5'[Product])

        Use this for your slicer. Then, create these two measures and use them in your tables. 

         

        Normal Measure = 
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,1,BLANK())


Inverse Measure = 
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,BLANK(),1)

        See Table5, Table6 and Page 3 of attached.