topic Re: Visual Filter using a a value from a column after looking up another table in DAX Commands and Tips

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

fraserward — Fri, 17 Feb 2023 20:17:20 GMT

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.

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

FreemanZ — Fri, 17 Feb 2023 23:47:04 GMT

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:

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

FreemanZ — Fri, 17 Feb 2023 23:57:31 GMT

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