Forum Discussion

atiftanveer's avatar
atiftanveer
Frequent Visitor
4 years ago
Solved

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 ...
condition other table
Filter Table
  • amitchandak's avatar
    amitchandak
    4 years ago

    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/

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
    atiftanveer
    Frequent Visitor
    4 years ago

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