Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
evoges
New Member

"OR" relationship in filters

Hi

 

I'm new to PowerBI (about 54 minutes into it) and struggling to figure out how to set up a filter that mimicks the following SQL:

 

SELECT ...

WHERE tableA.field1 = 1

AND (tableB.field2 in ('A', 'B') OR tableC.field3 in ('C', 'D', 'E') )

 

I was trying to set up a calculated field that represents the OR condition ... but even that is proving quite hard to figure out without any decent clues.

 

Creating the filters I have figured out - it is just the "OR" involving fields from different tables that I am stuck on.

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi evoges,

 

Based on your description, you want to achieve a new table which meet the condition in SQL script you have written, right?


Assume Table2 and Table3 have “One to one” or “One to many” relationship with table1.In this condition, to achieve your requirement, please follow steps below:

  1. In Power BI Desktop, create relationships between table1 and other tables as shown in the following screenshots after loading data from database.1.PNG2.PNG
  2. Right-Click Table1 and select “New Column”, use DAX formula below:

Calculate field = IF(AND(Table1[field1] = 1, OR(RELATED(Table2[field2]) in {"A", "B"}, RELATED(Table3[field3]) in {"C", "D", "E"})), TRUE(), FALSE())

     3.Use the calculate field  to create a slicer, the result is as below and you can refer to PBIX file:

https://www.dropbox.com/s/qffdzbic1t5hf3s/For%20evoges.pbix?dl=0

 11.png

Best Regards,

Jimmy Tao

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

Hi evoges,

 

Based on your description, you want to achieve a new table which meet the condition in SQL script you have written, right?


Assume Table2 and Table3 have “One to one” or “One to many” relationship with table1.In this condition, to achieve your requirement, please follow steps below:

  1. In Power BI Desktop, create relationships between table1 and other tables as shown in the following screenshots after loading data from database.1.PNG2.PNG
  2. Right-Click Table1 and select “New Column”, use DAX formula below:

Calculate field = IF(AND(Table1[field1] = 1, OR(RELATED(Table2[field2]) in {"A", "B"}, RELATED(Table3[field3]) in {"C", "D", "E"})), TRUE(), FALSE())

     3.Use the calculate field  to create a slicer, the result is as below and you can refer to PBIX file:

https://www.dropbox.com/s/qffdzbic1t5hf3s/For%20evoges.pbix?dl=0

 11.png

Best Regards,

Jimmy Tao

stretcharm
Memorable Member
Memorable Member

I asume your in Dax (not M/Query Editor)

 

There is a DAX function OR as well as AND

https://msdn.microsoft.com/en-us/library/ee634951.aspx

 

e.g.

 

FilteredTable =
FILTER (
    MyTable,
    AND (
        OR ( MyTable[Column1] = "1", MyTable[Column2] = "x" ),
        MyTable[Column3] = "y"
    )
)

See some examples of filters here

https://www.sqlbi.com/articles/from-sql-to-dax-filtering-data/

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors