OR() function

[vshevch]

Hi,

I am using CALCULATE function to extract sum out of a column if it matches 1 out of 5 criteria. I used an OR() function to see if it matches any of those values. However, since DAX OR() function supports only 2 values, as opposed to excel OR() function, which supports multiple values, I had to make a nested OR statement - i.e. OR(OR(OR(value 1, value 2),OR(value 3, value 4),value 5). I was curious, is there a better way of doing this? Perhaps there is a more suitable function for this problem?

[nickchobotar]

Hello @vshevch

Yes there is a better way than nesting OR()s.  In my opinion, the easiest way to return your logic is to use the IN() operator which really simplifies the logical condtions.

I would write this measure like so:

= 
CALCULATE(
	<expression>, 
	'Current Champ Year and Month'[Current Champ Month] IN {3,6,9,12 }
)

Talking about IN() operator I think it's also good to mention its best friend CONTAINSROW() function.

You can re-write the measure above with CONTAINSROW() like so:

=
CALCULATE (
    <expression>,
    CONTAINSROW (
        {
            3,
            6,
            9,
            12 },
        'Current Champ Year and Month'[Current Champ Month]
    )
)

Thanks, Nick -

[TheOckieMofo]

I would get familiar with the SWITCH() function. It is very, very useful in situations like this. The basic syntax is:

Switch(expression,value1,result1,[value2],[result2],...[else])

Honestly, I've used this function so often that I probably have to have a conversation about it with my wife. We are really close. But let me break it down a little bit for you. The expression part of the function could be either a single column OR the dax function TRUE(). When using the second option, it opens up a world of awesome as described here on Collie's site: PowerPivotPro Switch TRUE. It allows you to do all sorts of things, including using multiple columns for conditional/logical analysis.

So, my point with all this (I promise there is one) is that the Switch function will work for you as it already applies a logical OR around each item in the list and it also gives you a catch "else" statement which you can use to lump all the cats and dogs together. Additionally, mastering the Switch TRUE combo will definitely come in handy down the road as you build more and more data models.

[MattAllington]

A generally better alternative than nested OR statements (in my view) is to use the double pipe symbol (above the enter key with the shift button).  With the double pipe, you can write  X = 1 || 2 || 3 || 5    This is semantically equivelant of =OR(1,2,3,5) in Excel

[Shelley]

Wouldn't it have to be X=1 || X=2 || X=3 || X=5?

Here's my example that worked:

Is Payout? = If('Current Champ Year and Month'[Current Champ Month] = 3 || 'Current Champ Year and Month'[Current Champ Month] =  6 || 'Current Champ Year and Month'[Current Champ Month] = 9 || 'Current Champ Year and Month'[Current Champ Month] = 12, "Yes", "No")

When I tried the following, it did NOT work:

Is Payout? = If('Current Champ Year and Month'[Current Champ Month] = 3 || 6 || 9 || 12, "Yes", "No")

[Greg_Deckler]

I'm thinking maybe SWITCH, but can you post your full formula so that I can see how you are using OR?