Forum Discussion

fraserward's avatar
fraserward
Frequent Visitor
3 years ago
Solved

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

  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...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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: