topic Re: Filter table rows based on condition in other table in DAX Commands and Tips

Filter table rows based on condition in other table

atiftanveer — Wed, 22 Jun 2022 13:19:36 GMT

Hi,

How to Write DAX for following SQL example:

Select Column1, Column2, Column3

from Table1 T1

Inner Join (

Select Column2 from Table2

Where Column3 Not Like '%ABC%'

And Column3 Not Like '%XYZ%'

) T2

ON T1.Column1 = T2.Column 1

Where T1.Column3 = '%EFG%'

Re: Filter table rows based on condition in other table

amitchandak — Thu, 23 Jun 2022 07:09:11 GMT

atiftanveer , Assume Table 1 and Table 2 are joined on the required column. You can use this measure with Column1, column 2, and Column 3 from table1 in a visual

calculate(countrows(Table), filter(table1, containsstring(table1[Column3], "EFG")),
filter(Table2, not(containsstring(table2[Column3], "XYZ")) && not(containsstring(table2[Column3], "ABC"))))

Also, refer

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Re: Filter table rows based on condition in other table

atiftanveer — Thu, 23 Jun 2022 14:52:18 GMT

thank you amit,

my requirement was row list, I made little changes in measure expression it worked

measure:

calculate(countrows(Table), filter(table1, containsstring(table1[Column3], "EFG")),
filter(Table2, not(containsstring(table2[Column3], "XYZ")) && not(containsstring(table2[Column3], "ABC"))))

table:

CALCULATETABLE((Table), filter(table1, containsstring(table1[Column3], "EFG")),
filter(Table2, not(containsstring(table2[Column3], "XYZ")) && not(containsstring(table2[Column3], "ABC"))))