cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
atiftanveer
Frequent Visitor

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors