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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ericOnline
Post Patron
Post Patron

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

2 REPLIES 2
lbendlin
Super User
Super User

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.

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.