OR operator in DAX

3 ways how to create OR logic in DAX

OR logic in DAX is frequently used in various Power BI calculations. The bad and the good news is that you can create that in multiple ways.
In this post is 3 of them, and you can decide which is the best one for you.

 

Here is my calculated table in DAX that you can recreate.

MySales = DATATABLE(
    "Store",STRING, "Sales",INTEGER,
{
    {"Sky1", 100},
    {"Sky2", 100},
    {"Moon", 100},
    {"Mars", 100},
    {"Cloud", 100}
})

 

OR function in DAX

It might be the first option that comes to mind when it is about creating OR logic in DAX. The same function is in Excel, but there is a difference between the OR function in DAX. The main problem with the OR function in DAX might be that there are only 2 arguments.

MoonMarsSales1 = 
SUMX (
    FILTER ( MySales, OR ( MySales[Store] = "Moon", MySales[Store] = "Mars" ) ),
    MySales[Sales]
)

If you need more than 2 arguments, then do not create nested OR functions. Firstly take a look at the next two options.

 

OR logic in DAX with OR operator

OR operator in DAX is represented with a double pipe symbol. The same logic as in the previous example looks like this.

MoonMarsSales2 = 
SUMX (
    FILTER ( MySales, MySales[Store] = "Moon" || MySales[Store] = "Mars" ),
    MySales[Sales]
)

It is handy to create OR logic in DAX with 2 or more arguments.

EarthSales1 = 
SUMX (
    FILTER ( MySales, MySales[Store] = "Sky1" 
                    || MySales[Store] = "Sky2" 
                    || MySales[Store] = "Cloud" ),
    MySales[Sales]
)

If you have a long list of possible arguments that you would like to use in OR logic in DAX, look at the next option.

 

OR logic in DAX with IN operator

When creating OR logic in DAX, IN operator is my absolute favorite. Of course, it works if there is something equal to some of the multiple options. In the case of more complex logic, the OR operator as a double pipe symbol is the best choice.
To create OR logic with IN operator, write all necessary values in curly brackets and separate them with a comma like below.

EarthSales2 = 
SUMX (
    FILTER ( MySales, MySales[Store] IN {"Sky1", "Sky2", "Cloud"} ),
    MySales[Sales]
)

Let’s say that you have a column with values that you want to use in OR logic. Here is a calculated table for that.

StoreList = DATATABLE(
    "GetStore",STRING,
{
    {"Sky1"},
    {"Sky2"},
    {"Cloud"}
})

You can use IN operator with values from the necessary column. It is handy if you have a dynamic list of values that you want to use.

EarthSales3 = 
SUMX (
    FILTER ( MySales, MySales[Store] IN VALUES ( StoreList[GetStore] ) ),
    MySales[Sales]
)

IN operator does not use wildcard characters, but you can use them in functions like CONTAINSSTRING.

EarthSales4 = 
SUMX (
    FILTER ( MySales, CONTAINSSTRING ( MySales[Store], "Sky*" ) ),
    MySales[Sales]
)

 

DAX NOT IN operator

If you are looking for a NOT IN operator in DAX, then there is not exactly that. You can get that with NOT function.

NotEarthSales = 
SUMX (
    FILTER ( MySales, NOT ( MySales[Store] IN { "Sky1", "Sky2", "Cloud" } ) ),
    MySales[Sales]
)

 

Are you dealing with many measurements in Power BI? Here is how to organize them.





Posted

in

,

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *