Forum Discussion

ericOnline's avatar
ericOnline
Icon for Post Patron rankPost Patron
6 years ago

FILTER w/ Nested AND() and OR()?

I'm coming from the Power Apps world. If there is an EMPLOYEE_TABLE I need to filter by JobTitle and Status, I'd do something like:   FILTER(EMPLOYEE_TABLE, AND( OR( JobTit...
General Comment
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

You can have any combination of filters  that you want, without ever using AND() or OR().

 

Simply use brackets, IN , && and ||.

 

(A || B ) && (C || D)  && E IN (...)

 

etc.

 

Also note that CALCULATE allows you to use any number of filters separated by comma, with AND implied.

 

NOTE: (!!!) The storage engine is optimized for "simple"  AND filters.  Anything beyond that will fire upt the formula engine. Always check the preformance of your resulting measure.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I would just add a comment to the response by lbendlin .  Power BI is based on a columnar model, so DAX works better when working on one column at a time.  In your example, you were filtering on two different columns (status and job title).  It is a good practice if possible to nest Filter()s together to do one at a time.  Of course, if you use CALCULATE, you can work on multiple columns and it takes care of it behind the scenes.

     

    FILTER(FILTER(Table, Table[Column1] IN {"Value1", "Value2"}), Table[Column2] = "A" || Table[Column2] = "B")

     

    Regards,

    Pat