"OR" relationship in filters

[evoges]

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.

[v-yuta-msft · Accepted solution]

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.
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

 

Best Regards,

Jimmy Tao

[stretcharm]

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/