cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ggranger
Advocate I
Advocate I

Slicer to filter a slicer in an unrelated table

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!

1 ACCEPTED SOLUTION
PaulDBrown
Super User II
Super User II

@ggranger 

 

You can try something like this.

First the model:

Model.PNG

 

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:

Result.PNG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

9 REPLIES 9
PaulDBrown
Super User II
Super User II

@ggranger 

 

You can try something like this.

First the model:

Model.PNG

 

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:

Result.PNG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

@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.

@ggranger 

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:

no selections.PNG

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))

 

 

both.PNG

 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:

table filter.PNG

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

final filters.PNG

 

 

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

BM1.jpg
BM2.jpg

 

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.

@ggranger 

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:

New model.JPG

 

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

Table 1.JPGTable2.JPG

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:

noselection.JPG

 

b. Either slicer selected:

Sicer t2.JPG

Slicer t1.JPG

c. combination of slicers

combines slicers.JPG

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.

Sort by index.JPG

 

I've attached the sample PBIX file for you





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@ggranger 

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? 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






parry2k
Super User III
Super User III

@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. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User III
Super User III

Hi @ggranger ,

 

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.

 

MFelix_0-1611426853424.png

 

 

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.

MFelix_1-1611426875824.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors