Visual Filter using a a value from a column after looking up another table

[fraserward]

I need some dax that I can use as a visual filter to filter out rows based on this logic.

Table 1

Name	REF	Sort
A	A1001	1
B		1
C		1
D	A1002	2
E		2
F	A1003	3

Table 2

REF

A1001

A1002

I want to filter Table 1 rows, If the record exists in Table 1 and Table 2 then filter out all rows from Table 1 with the same Table1 Sort value.   

Looking at the Ref column in both tables we can see A1001 exists in both tables.  From here I need to find the value in the Sort Column for Ref A1001 and whatever that value is, filter all rows with that value.  In this case that is value 1, removing 3 rows.

[FreemanZ · Accepted solution]

or your write a Tag column and use the Tag column to filter table1, like this:

Tag = 
VAR _reflist =
FILTER(
    VALUES(Table1[REF]),
    CONTAINS(Table2, Table2[REF], Table1[REF])
)
VAR _sortlist =
CALCULATETABLE(
    VALUES(Table1[Sort]),
    ALL(table1),
    Table1[REF] IN _reflist
)
RETURN
IF(
    [Sort] IN _sortlist,
    "Yes", "No"
)

[FreemanZ]

hi @fraserward 

try to add a calculated table like:

CalculatedTable = 
VAR _reflist =
FILTER(
    VALUES(Table1[REF]),
    CONTAINS(Table2, Table2[REF], Table1[REF])
)
VAR _sortlist =
CALCULATETABLE(
    VALUES(Table1[Sort]),
    Table1[REF] IN _reflist
)
RETURN
CALCULATETABLE(
    Table1, 
    Table1[Sort] IN _sortlist
)

it worked like: