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

in hive-102332 •  6 months ago  (edited)

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.

DAX AND OR IN && ||

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.

conditional statement in DAX

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

dax and statement

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.

DAX and syntax

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.

DAX and conditional logical test

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.

DAX or logical test

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.

DAX or

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}

Power Pivot IN function

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.

DAX IN

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

DAX for powerpivot course
Take A FREE course with us Today!
FREE beginner excel training
The Ultimate Excel Formulas Course
* includes XLOOKUP and will soon include Dynamic Arrays



Become a Power Pivot Hero

Power Pivot online training course

GET and TRANSFORM DATA like a PRO

Power Query Excel 365



Learn DAX for Power Pivot and Power BI

DAX for powerpivot course
 

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/
Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Classify =
if(
Sales[Units Sold]>45 && Sales[Product] ="Scooter"
|| Sales[Units Sold]>25 && Sales[Product] in {"Skateboard","Dollhouse"}
|| Sales[Units Sold]>20 && Sales[Product] = "Bike",
"Large Shipment","Standard Shipment"
)
- Sairam Balasubramaniam

This comment was made by a guest account using @steempress. Rewards will be sent to the user once they have a Steem account

Nice work Sairam

Classify =
if(
Sales[Units Sold]>45 && Sales[Product] in {"Scooter"}
|| Sales[Units Sold]>25 && Sales[Product] in {"Skateboard","Dollhouse"}
|| Sales[Units Sold]>20 && Sales[Product] in {"Bike"},
"Large Shipment","Standard Shipment"
)


- Sairam Balasubramaniam

This comment was made by a guest account using @steempress. Rewards will be sent to the user once they have a Steem account

Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You distributed more than 22000 upvotes. Your next target is to reach 23000 upvotes.

You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Do not miss the last post from @steemitboard:

SteemitBoard Ranking update - A better rich list comparator
Vote for @Steemitboard as a witness to get one more award and increased upvotes!