Filter table rows based on condition in other table

[atiftanveer]

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%'

[amitchandak · Accepted solution]

@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/

[atiftanveer]

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