Slicer to filter a slicer in an unrelated table

[Anonymous]

I have two tables that are unrelated. Table A and Table B.

Each table has a slicer on the field "Animals."

Table A

Animals

O - Cat

O - Dog

O - Elephant

Table B

Animals

O - Cat

O - Dog

O - Elephant

Each table has a checkbox slicer for all of the values. When I select "Cat" in the Table A slicer, I want "Cat" to disappear in Table B's slicer. The user can also select multiple values and these would all be excluded in the second slicer.

Example:

Table A Slicer

Animals

X - Cat

O - Dog

O - Elephant

Table B Slicer

Animals

O - Dog

O - Elephant

Is it possible to do this in PowerBI?

Fix2: Wow just typed that all in and have to retype this because I was not logged in!

[PaulDBrown · Accepted solution]

@Anonymous 

You can try something like this.

First the model:

Then the measure:

Exclude Selection = 
VAR selValTable1 = VALUES(Table1[Animal])
VAR selValTable2 = VALUES(Table2[Animal])
Return
IF(ISFILTERED(Table1[Animal]), COUNTROWS(EXCEPT(selValTable2, selValTable1)), COUNTROWS(Table2))

Add this measure to the "filters on this visual" and set the value to 1

And you get  this:

[Anonymous]

@PaulDBrown You sir, are a genius! I was starting to play with the EXCEPTION field, but was still far away from the solution.

One thing I should have asked - is there a way to show all of the values in both slicers if nothing is selected (e.g. all the values are available in both tables)? The problem I am facing is if I apply this to both slicers, they will both show blank. 

Edit: Tweaked above to this:

Exclude Selection =
VAR selValTable1 = VALUES(Table1[Animal])
VAR selValTable2 = VALUES(Table2[Animal])
Return
IF(ISFILTERED(Table1[Animal]), COUNTROWS(EXCEPT(selValTable2, selValTable1)), 1)

Edit2: I did not ask this, but I was expecting this to remove the data from everything the slicer filters as well. This filters only the slicer.

[PaulDBrown]

@Anonymous 

The measure is written to be applied only on the "filters of this visual" of the slicer of the second table. (don´t add it to the slicer of the first table).

If no items from the first slicer are selected, you will get all the items in both slicers:

If you want the same behaviour acting from slicer 2 to slicer 1, you need an equivalent measure and apply it to the "filters on this visual" for slicer 1, setting again the value to 1:

Exclude Selection table 1 = 
VAR selValTable1 = VALUES(Table1[Animal])
VAR selValTable2 = VALUES(Table2[Animal])
Return
IF(ISFILTERED(Table2[Animal]), COUNTROWS(EXCEPT(selValTable1, selValTable2)), COUNTROWS(Table1))

 In the above example, each slicer has it's corresponding measure as a filter: each measure is written specifically for each slicer; you can't use the same measure on both slicers.

As regards the filtering, my depiction was a bit confusing. I de-activated interactions on the tables on the left to show the complete tables. If you have the interactions between visuals active (default behaviour), each table will be filtered by its corresponding slicer, as you can see in the depiction above.

To filter the "opposite table's values", based on the slicer selection, apply each corresponding measure to the "filters on this visual" for each table (the same measure you have used as a filter for each table's corresponding slicer:

So table 2 now has the same filter (measure) used for slicer 2; and do the same for table 1 by adding the measure you have used for slicer 1 as a filter on table 1. Make sense?

If you then apply a filter to slicer 2, the values in table 2 will filter accordingly

Ps: maybe I should do a blog post about this?

[Anonymous]

Hey Paul, thanks again for your help! Your explanation above makes sense and I see where I am not explaining my entire goal well. I am 90% of the way there.

Challenge I am having is I have combined variables from Table1 and Table2 in the same graph. So for the example above, there would be an additional column for "Animal Count." 

If I select "Bird" in Table 1, Table 1 is still showing in the visualization above. Here is a picture of how I have my data setup.

Using your code, I created this additional measure to try and filter the visualization. It adds the two measures together and if they add up to "2" or nothing is filtered the animals would display in the slicer. This almost works. However it does not work when I add sources from both tables, because the tables are unrelated:

Exclude Animals (Group1+Group2) =
VAR selValTable1 = VALUES(Table1[Animal])
VAR selValTable2 = VALUES(Table2[Animal])
Return
IF(AND(ISFILTERED(Table1[Animal]),ISFILTERED(Table2[Animal])),2,IF(ISFILTERED(Table1[Animal]), COUNTROWS(EXCEPT(selValTable2, selValTable1)), 1)+
IF(ISFILTERED(Table2[Animal]), COUNTROWS(EXCEPT(selValTable1, selValTable2)), 1))

Do you see any way I could achieve the intended effect? Maybe create relationships through code? I am keeping the data tables unrelated so I can do comparisons in the same visual.

I imagine a blog post would be very helpful for others. Did not see this answered anywhere else, but I'm sure I'm not the first person to have this question.

[PaulDBrown]

@Anonymous 

Ok, I think I get what you are trying to reproduce: show the count of animals in the same visual. This actually makes things slightly more complicated (at least the way I've worked through it, but I may well be over-complicating things myself!)

I'll walk through this step by step. 

1) Create a new table including the unique values for the animals from both tables (you can do this by appending tables in Power Query or using the following DAX:

List Animals =
VAR Table1Anim = VALUES(Table1[Animal])
VAR Table2Anim = VALUES(Table2[Animal])
RETURN
DISTINCT(
      UNION(Table1Anim, Table2Anim)
)

We will use this table for the axis of the visuals. It must remain unrelated to the other tables in the model:

Here are the two sample tables I am using in this exercise:

2) Create the simple measures for the number of animals:

Number of Animals T1 = SUM(Table1[Count])

Number of Animals T2 = SUM(Table2[Count])

3) Use the measure we created previously to filter the slicers (and add them to the filter pane when you create each slicer):

Exclude T1 Selection from T2 =
VAR selValTable1 =
    VALUES ( Table1[Animal] )
VAR selValTable2 =
    VALUES ( Table2[Animal] )
RETURN
    IF (
        ISFILTERED ( Table1[Animal] ),
        COUNTROWS ( EXCEPT ( selValTable2, selValTable1 ) ),
        COUNTROWS ( Table2 )
    )

Exclude T2 Selection from T1 =
VAR selValTable1 =
    VALUES ( Table1[Animal] )
VAR selValTable2 =
    VALUES ( Table2[Animal] )
RETURN
    IF (
        ISFILTERED ( Table2[Animal] ),
        COUNTROWS ( EXCEPT ( selValTable1, selValTable2 ) ),
        COUNTROWS ( Table1 )
    )

4) Since we are using the List Animals unrelated table for the axis, we need to create measures using TREATAS to filter the corresponding columns from each table:

Count Animals 1 =
CALCULATE (
    [Number of Animals T1],
    TREATAS ( VALUES ( 'List Animals'[Animal] ), Table1[Animal] )
)

Count Animals 2 =
CALCULATE (
    [Number of Animals T2],
    TREATAS ( VALUES ( 'List Animals'[Animal] ), Table2[Animal] )
)

5) Finally, we need the following measures to emulate the filtering of the opposite tables and rendering the correct values for each selection made in the slicers. These are the measures we will use in the final visuals

Count of Animals (Table1) =
VAR selValTable1 =
    VALUES ( Table1[Animal] )
VAR selValTable2 =
    VALUES ( Table2[Animal] )
VAR ListAnimals =
    VALUES ( 'List Animals'[Animal] )
VAR FIlt =
    IF (
        ISFILTERED ( Table2[Animal] ),
        COUNTROWS ( INTERSECT ( ListAnimals, EXCEPT ( selValTable1, selValTable2 ) ) ),
        COUNTROWS ( INTERSECT ( ListAnimals, selValTable1 ) )
    )
RETURN
    CALCULATE ( [Count Animals 1], FILTER ( 'List Animals', FIlt = 1 ) )

Count of Animals (Table2) =
VAR selValTable1 =
    VALUES ( Table1[Animal] )
VAR selValTable2 =
    VALUES ( Table2[Animal] )
VAR ListAnimals =
    VALUES ( 'List Animals'[Animal] )
VAR FIlt =
    IF (
        ISFILTERED ( Table1[Animal] ),
        COUNTROWS ( INTERSECT ( ListAnimals, EXCEPT ( selValTable2, selValTable1 ) ) ),
        COUNTROWS ( INTERSECT ( ListAnimals, selValTable2 ) )
    )
RETURN
    CALCULATE ( [Count Animals 2], FILTER ( 'List Animals', FIlt = 1 ) )

Now you can set up the visuals using the 'List Animals' [Animal] field in the axis and the final measures. Create the slicers with the corresponding measure to filter, and you get this

a. No selection:

b. Either slicer selected:

c. combination of slicers

PS. If you want to keep the sorting order in the visuals consistent, add an index column to the 'List Animals' table and sort the table by the index column and the visuals by that animal column.

I've attached the sample PBIX file for you

[PaulDBrown]

@Anonymous 

Sure, you can create virtual relationships between tables using DAX: the TREATAS function is your best friend in this case. 

I'm not too sure what you are trying to depict in the bar chart visual though (or in the measure itself). Can you show another example with an explanation of the intended outcome? 

[parry2k]

@MFelix Great solution but the only challenge I see that it will not work in slicer with multi-selection so need some improvement. Just my 2 cents. 

[MFelix]

@parry2k you are correct, however I made this like this in order to show the error when you have selection and you want to filter out increasing the number of selections will increase this error type. But you are very much correct. 

[MFelix]

Hi @Anonymous ,

This is possible using the following syntax:

Filter_Slicer = if(SELECTEDVALUE(TableA[Animal]) = SELECTEDVALUE(TableB[Animal]), BLANK() , 1)

Then use this measure has a filter on TAble B and select all non blank values.

However slicers have a special way of working when compared with other visualizations so if you have a value selected on the table B slicer and is the one you want to hide until you select other option the value will continue to appear.