DAX AND OR IN or syntax(&& ||) – Which one?

AND, OR and IN are common statements used in DAX to create conditional logical tests.  Most DAX functions work the same as their counterpart in Excel, however AND and OR work a little different in DAX. For some crazy reason they have a major limitation compared to the same Excel function. But with a little DAX syntax knowledge we can quickly work around this limitation.

In this article we will look at the AND function and syntax in DAX.  Then we will look at the OR function and syntax and finally you will be introduced to the IN function.

We also have a video to go along with this which you can view here

AND function and Syntax in DAX

The AND statement in DAX checks to see if two conditions are met.  In order to get a true result. Both the condition must be satisfied for a true result to be returned.

The DAX syntax for AND is

=AND(Logical test 1, Logical test 2)

Lets take a look at an example.  We have a simple table of data, which we have named TbData.

This table contains 3 columns, Record 1, Record 2 and Record 3.  

2 Criteria AND statement

We wish to create a calculated column that will return a true where Record 1 = Record 2 AND Record 2 = Record 3.

Using the syntax

=AND(logical test 1, logical test 2)

Our first logical test will be

 'TbData'[Record 1] = 'TbData'[Record 2],

And our second

  'TbData'[Record 2] = 'TbData'[Record 3]

You can see the results in the image. As expected, the last row is the only row to return a true as this is the only row where both conditions are met.

3 criteria AND statement

Suppose now our test is to see if Record 1 = Record 2 AND Record 2 = Record 3 AND Record 3 = Record 1.  We now have 3 conditions to be met to get a true result.

Taking the function we used above we can add 1 further test

'TbData'[Record 2] = 'TbData'[Record 3]

But look what happens!!!!

When we try to enter this into DAX using a third condition with the AND function, we get an error. Can you spot the limitation?  Yep that’s right, only two logical tests.  This is very different to Excel where you can use multiple logical tests within an AND statement.

AND Syntax

To over come this limitation there is a syntax for AND that will allow you add multiple conditions.  This syntax is &&.  If you have more than 2 conditions to be met, you can forget about the AND statement and start working with a little DAX syntax.

Take a look at how we would write the sytax that will test if Record 1 = Record 2 AND Record 2 = Record 3 AND Record 3 = Record 1.

As you can see, we don’t reference a DAX function.  We literally write the logical expression and combine each test with &&.

If you made it this far, nice work, there will be a practice learn and earn activity at the end of this post so please do continue.

OR function and Syntax in DAX

The DAX syntax for OR is

=OR(Logical test 1, Logical test 2)

The OR functions tests to see if either of the conditions are true, in which case a true value will be returned. Just like the AND function, the OR function in DAX will only take 2 conditions.

Let’s say this time we wish to test if Record 1 = Record 2 Or Record 2 = Record 3.  As there are only 2 conditions we can use the OR function as shown in the image.

However, if you have more than 2 criteria to test, you must use the syntax for OR which in DAX is ||.

Take a look at how we would use || to test if Record 1 = Record 2 OR Record 2 = Record 3 OR Record 3 = Record 1.

Easy right!  All you have to remember is that you drop the use of the OR function and use || between the different logical tests.

DAX IN function

The IN function is another logical function.  The syntax is a bit odd compared to other DAX functions and it is not a function you will find in Excel.

The IN function will test to see if a set of paramaters are contained within an Expression or scalar.

You would expect, following the pattern of other DAX expression the syntax for in to be something like

=IN(something, something)

However, its very different.

When using IN we start with the Expression or Scalar and then using IN {we list the paramaters}

Lets look at an example.  We wish to test if the column for Record 1 contains the values 25 or 31

One option is to use the OR function as shown in the image below.

However, in this case we can replace the OR function with the use of IN.

With IN we start with the expression, in this case will be TbData[Record 1] and we want to see if the values 25 or 36 are contained in the values for each row.

IN = TbData[Record 1] in {25,36}

Let’s add a little more logic to this.  Using IN we can test to see if Record 1 contains 25 OR 67 AND if Record 2 contains 25 OR 67. 

Before we do this using IN, first look at how we can do this using the || for OR.  As you can see below, it’s not that hard to achieve and we don’t require not too many lines of code. But I have still named this measure Or complex.   I have done so as we have to repeat 25 and 67 and we also have to repeat both tables.

Using the syntax || and && is more complex than using IN. By using IN we are eliminating the need to repeat the values our conditions (25 and 67 in this case) and in this example we also eliminate the need to repeat the table names.

Using IN in this way makes your code shorter and you more efficient.

Wrap up

In a way the difference between how AND and OR work in Excel and DAX is a little annoying and requires a little getting use to by the Excel user.  However, we can see from the examples, the use of && and || are easy to read.  Just like the use of IN.  Once you understand it, using this syntax and using IN will become second nature.  And shorten your code.

Which brings me to my next point.  DAX is code. If you have been an Excel user and are now learning DAX, you can officially say you are learning to code.  Using syntax like && and || and using the function IN are very code like.  One thing I am trying to embrace more myself is to write DAX so it looks like code.  I think I have done rather well in today but find it hard to break the habit of writing a DAX function the way I would write an Excel function.  So if you are new to DAX, try get into the habit of writing DAX like code because if you are using DAX for a while like me, these bad habits are hard to break.

DAX Learn and Earn Activity

Download the PowerBI file here.

The Power BI file contains the following table of data.  I have called this table Sales.  It’s a simple table showing invoice details such as the product and the units sold. 

It is your job to create a calculated column to classify the sales invoices into the following shipment information

Large Shipment  >45 units  of scooters or >25 units of Dolls House or skateboard or >20 units of bikes.

Classify all other size orders as standard shipment

To Earn while you learn on this activity in the comments section below answer the following questions

• What code you have used to create this column?
• List 3 takeaways from this post

READY FOR THE VIDEO SOLUTION? WATCH THE VIDEO NOW

Learn DAX for Power Pivot and Power BI

Take A FREE course with us Today!
The Ultimate Excel Formulas Course * includes XLOOKUP and will soon include Dynamic Arrays

Become a Power Pivot Hero


GET and TRANSFORM DATA like a PRO
Power Query Excel 365

Learn DAX for Power Pivot and Power BI

 

Best Value Excel and Excel Power Tool Learning.  Access All Areas, Unlimited Learning Subscription

 

If you would like to start collecting rewards quickly for learning Excel  then you should try:

10+ Excel Learn and Earn Activities YOU can do Today

SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS & LEARN AND EARN ACTIVITIES TO YOUR INBOX

SIGN UP

---

Cross posted from my blog with SteemPress : https://theexcelclub.com/dax-and-or-in-or-syntax-which-one/

---