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

[ericOnline]

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(
            JobTitle = "DECKHAND",
            JobTitle = "CAPTAIN",
            JobTitle = "BOWMAN",
            ...etc.
        ),
        Status <> "DISMISSED",
        Status <> "RETIRED",
        Status <> "RESIGNED"
        ...etc.
    )
)

...where I could put 20 conditions in either the AND() or OR().
 

But here in the Power BI world, it appears that AND() and OR() only allow two condition checks per statement. Am I correct here? Would I need to write the above in the (quite complex to read/write/debug) format of:

FILTER(EMPLOYEE_TABLE,
    AND(
        AND(
            OR(
                OR(
                    JobTitle = "DECKHAND",
                    JobTitle = "CAPTAIN"
                ),
                OR(
                    JobTitle = "BOWMAN",
                    ...etc.
                )
            ),
            Status <> "DISMISSED"
         ),
         AND(
            Status <> "RETIRED",
            Status <> "RESIGNED"
        )
    )
)

 (I'm nearly certain there is a missing "," or ")" above as I'm just starting to learn DAX.)
Is there a more efficient way to handle AND() and OR()'s in DAX?
Thank you

[lbendlin]

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]

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