Forum Discussion

pbhat89's avatar
pbhat89
Icon for Helper II rankHelper II
5 years ago
Solved

Cross filtering across tables

My one table is given in the format below  ID Age Height Score 1   20     167     120 2   30     150     122 3   40     190      100   Table 2 is  ID Condition 1    A 1    A 2    B 3    C ...
Need Help
Show and Tell
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    Hi pbhat89 ,

     

    Do you want the result is the measure or the table?

    If the measure is your result, please refer the following steps.

     

    1. Change the Both direction to Single.

     

     

    2. Then create a measure like this,

     

    Measure = 
var _selected = SELECTEDVALUE('Table 2'[Condition])
var _select_count = CALCULATE(COUNT('Table 1'[ID]),FILTER('Table 2','Table 2'[Condition]=_selected))
var _Total = COUNT('Table 1'[ID])
return
_Total - _select_count

     

     

     

    If you have any question, please kindly ask here and we will try to resolve it.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

     

    BTW, pbix as attached.

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago
It would be easier just to change the selection controls on the slicer, and turn off single select with ctrl and adjust a few other things to get it how you want.

If you absolutely need to restrict them to a single item to exclude, then you will need to create a slicer table that is NOT related to the data so that when you select an item other items are still visible;
Create a NEW TABLE:
Slicer = VALUES(table[item])

Then you'll need to create a measure, possibly using:

Items to show = IF(SELECTEDVALUE(table[Item]) NOT IN Values(slicer[item]), "Yes", "No")

Finally, use that measure to filter the visuals.
pbhat89's avatar
pbhat89
Icon for Helper II rankHelper II
5 years ago

Understand your approach and makes sense. I was able to create a slicer first with below :

 

Disclosures = DISTINCT(VALUES(Table2[Condition]))

 

Now for the measure : i believe the selected value should be on the slicer - since i will select the ones i want to be filtered out in table 2 i.e. condition ?  And "NOT IN" is not working in DAX.  What is the correct approach for creating this measure ? Below my attempt which gives error on NOT IN

 

Filter_condition = IF(SELECTEDVALUE(Disclosure[Condition]) NOT IN Values(Table2[Condition]), "Yes", "No")