Forum Discussion
Filter table rows based on condition in other table
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%'
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/
2 Replies
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/
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"))))
