Forum Discussion
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 | 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.
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" )
2 Replies
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" )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: