- 2 minutes to read
Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns false.
|logical_1, logical_2||The logical values you want to test.|
Returns true or false depending on the combination of values that you test.
The AND function in DAX accepts only two (2) arguments. If you need to perform an AND operation on multiple expressions, you can create a series of calculations or, better, use the AND operator (&&) to join all of them in a simpler expression.
The following formula shows the syntax of the AND function.
Because both conditions, passed as arguments, to the AND function are true, the formula returns "All True".
The following sample uses the AND function with nested formulas to compare two sets of calculations at the same time. For each product category, the formula determines if the current year sales and previous year sales of the Internet channel are larger than the Reseller channel for the same periods. If both conditions are true, for each category the formula returns the value, "Internet hit".
|Row Labels||2005||2006||2007||2008||-||Grand Total|
|Bike Stands||Internet Hit|
|Bottles and Cages||Internet Hit|
|Tires and Tubes||Internet Hit|
Logical Operators and Nested IFs in Power BI / Power Query
In the previous post I showed you guys how to create a conditional column in Power BI / Power Query using the UI and then just using the Power Query Formula language.
In this post we’ll go over the available conditional operators and how to do Nested IFs in Power BI / Power Query.
What are Logical Operators?
You’ve probably seen them sometime in DAX or in the Excel formula language and some of those are:
but how do you write them in the Power Query formula language? we already know that we can only use them inside a Custom Column, but how will that look like?
Logical Operators in Power BI / Power Query
Let’s do a few tests to see how these operators work. I have this simple table that I’ll use asan example:
One thing to take in consideration before you try these by yourself, Power Query formula language (also known as M), is case sensitive.
Test 1: Using the AND operator
We’ll be creating a new column to check if the value in this column is greater than 8 AND less than 25. That will look like this using a Custom Column:
[Number] > 8 and [Number] < 25
and the result of that will look like this:
Note how the output is logical value, either a TRUE or a FALSE.
Test 2: Using the OR operator
Now we want to create a new column that will test if the value is either less than 15 or greater than 25.
That into M code will look like this:
[Number]< 15 or [Number] > 25
and the result of that will look like this:
and yes! it gives us the correct answer again
Test 3: Using the NOT operator
For this final test, let’s find all the values that are NOT below 25.
That into M code will look like this:
not ([Number] < 25)
something really important about this formula is that I have the initial test in parenthesis, and what not does is simply shift the logical value to the opposite of that.
The result of that will look like this:
Common use cases of the logical operators
You may have seem these logical operators in use before. If you’ve ever done a filter in a table, check out what the formula bar says:
Yes – when it comes to filters, the logical operators can sometimes be used. Specifically when you need to select multiple values or parameters for a filter expression.
Apart from this, these logical operators are commonly used in IF statements, so let’s take a look at them.
Nested IF statements
Now that we know what the logical operators are and how to use them, let’s try and use them in a more practical way.
Doing a recap on how if statements work in Power Query, you have the following formula:
if <test> then <result if true> else <result if false>
The result of the <test> must be a TRUE or FALSE, or in other words, a logical value.
With that in mind, for the <result if true> or the <result if false> you can absolutely use another if statement without any issues.
Practical scenario: Calculating the Shipping cost of an order
Imagine that we have this table:
and from it we need to calculate the Shipping cost based on this logic:
Translating that from M into just plain English:
- if the Account of the order is Prime AND the weight is under 5kg AND the amount is higher than 100, then the shipping cost for the customer will be 0 (FREE SHIPPING!)
- if the previous doesn’t occur, then if the account is Prime AND the amount is over 200, then the shipping cost is 0 (FREE SHIPPING!!)
- if neither of those occur, then just use a standard formula to calculate the shipping which is Weight times 1.25
The result of that is:
Pretty simple, yet super powerful to understand how to use these logical operators. You can combine them however you want and in the way that is more practical or makes more sense to you.
Be sure to check the next post in this series where I’ll cover error handling or the pseudo IFERROR function in Power Query
- 2014 chevy malibu recall 2020
- Mercedes repair roswell, ga
- After effects music video templates
- Fort myers 20 day forecast
- 2016 chrysler 300 rear diffuser
The reason your formula is not working is that AND accepts only 2 arguments!
I personally prefer @MFelix's SWITCH approach using && easier to ready (for me at least)
But you could try this...Column =
Data[BL Date] > DATE ( 2018, 8, 31 )
AND ( Data[BL Date] <= DATE ( 2018, 8, 31 ), Data[Actual Date] <> 0 ),
AND ( Data[BL Date] <> 0, Data[Actual Date] <> 0 , Data[Variance] <= 0 ),
@StuznetLet me know if this actually works!
EDIT: @MFelix I believe you need to add this condition...nested if = IF ( Data[BL Date] > DATE ( 2018, 8, 31 ), "Future", IF ( Data[BL Date] <= DATE ( 2018, 8, 31 ) && Data[Actual Date] <> 0, "Late", IF ( Data[BL Date] && Data[Actual Date] <> 0 && Data[Variance] <= 0, "On-Time", "Late" ) ) )
And...SWITCH FORMULA = SWITCH ( TRUE (), Data[BL Date] > DATE ( 2018, 8, 31 ), "Future", Data[BL Date] <= DATE ( 2018, 8, 31 ) && Data[Actual Date] <> 0, "Late", Data[BL Date] && Data[Actual Date] && Data[Variance] <= 0, "On-Time", "Late" )
Checks a condition, and returns one value when it's TRUE, otherwise it returns a second value.
|logical_test||Any value or expression that can be evaluated to TRUE or FALSE.|
|value_if_true||The value that's returned if the logical test is TRUE.|
|value_if_false||(Optional) The value that's returned if the logical test is FALSE. If omitted, BLANK is returned.|
Either value_if_true, value_if_false, or BLANK.
The IF function can return a variant data type if value_if_true and value_if_false are of different data types, but the function attempts to return a single data type if both value_if_true and value_if_false are of numeric data types. In the latter case, the IF function will implicitly convert data types to accommodate both values.
For example, the formula returns TRUE or 0, but the formula returns only decimal values even though value_if_false is of the whole number data type. To learn more about implicit data type conversion, see Data types.
To execute the branch expressions regardless of the condition expression, use IF.EAGER instead.
The following Product table calculated column definitions use the IF function in different ways to classify each product based on its list price.
The first example tests whether the List Price column value is less than 500. When this condition is true, the value Low is returned. Because there's no value_if_false value, BLANK is returned.
Examples in this article can be added to the Power BI Desktop sample model. To get the model, see DAX sample model.
The second example uses the same test, but this time includes a value_if_false value. So, the formula classifies each product as either Low or High.
The third example uses the same test, but this time nests an IF function to perform an additional test. So, the formula classifies each product as either Low, Medium, or High.
When you need to nest multiple IF functions, the SWITCH function might be a better option. This function provides a more elegant way to write an expression that returns more than two possible values.
SWITCH function (DAX)
Statement bi power and if
Power BI IF Statement
Introduction to Power BI IF Statement
IF is the most popular statement in Excel & Power BI. Arriving new columns based on multiple conditions is almost impossible without IF Statements, so one needs to be aware of if statements while arriving new columns.
In Power BI IF statement is used as DAX function as well as Power Query conditional column, so in this article, we will take you through how to use the IF statement in Power BI to arrive new conditional columns.
What is the IF Statement in Power BI?
IF is a logical function or statement which is often used in MS Excel, coding languages and also in Power BI. In Power BI it is available in two ways, one is in terms of DAX function and another one is in terms of Power Query tool to add a new column based on conditions.
You must have put hours while learning it in MS Excel but with Power Query editor you will find it dead easy. Also, one thing we assume while writing this article is you are already aware of IF function in MS Excel.
How to Use the IF Statement in Power BI?
Below are the different examples to use If Statement in Power BI:
Power BI IF Statement – Example #1
For an example look at the below data table for this demonstration. So you can download the excel workbook from the below link which is used for this example.
Now we need to add a new column “Incentive” based on certain condition and that condition is as follows.
“If the sales value is >6500 then incentive will be 300 or else incentive will be 200”.
- Now I have already uploaded the above data table to Power BI Desktop file.
- Now we need to go to the “Power Query” editor to arrive conditional column. Under the Home tab click on Edit Queries.
- This will take you to the Power Query Editor window.
- In this new window go to the Add Column tab and choose Conditional Column.
- This will open a new conditional column criteria window as shown below.
- First, give a name to this column as “Incentive 1”.
- After this, we can see that the if statement already exists, so from the “Column Name” drop-down choose the “Sales Value” column.
- Next, choose the operator as greater than (>).
- Now mention the value as “6500”.
- Then the output will be an Incentive amount of 300.
- In the else statement add the incentive value as 200.
Now we can read this if statement with clear English i.e. “If Sale Value is greater than 6500 then Output is 300 Else 200.
This looks a lot easier than regular IF condition in MS Excel isn’t it??? Click on Ok to have a new conditional column.
There you go we have a new column as “Incentive” based on the given if conditions in Power BI.
Power BI IF Statement – Example #2
Now we have seen how to use if statement to arrive at a new column. Similarly, imagine the situation of arriving a new column based on multiple conditions.
For example “if the sales value is >6500 and region is “South” then we need the Incentive value as 400 or else if the sales value is >6500 then the incentive is 300 or else 200.
- So, in this case, we need to use the “Custom Column” option.
- This will open up the below window for us.
- As usual, give a name to New Column as “Incentive 2”.
- Now in the “Custom Column Formula” space, we need to write the “if Statement”, so first write the if statement and chose the Sale Value column.
- The first criteria are if the sale value is >6500, apply this logical test.
- Now we need to apply one more logical condition as well i.e. if the region is “South”, we need to use AND statement here.
- Then the Incentive will be 400.
- Else if we need to do another logical test of whether sales value is >6500 or not, if yes then the incentive will be 300 or else 200. So apply one more if statement like the below.
Ok, in this statement first we will test whether sales value is > 6500 and region is “South’ or not if both are correct then the Incentive amount will be 400 or else again it will test whether sales value is > 6500 or not if yes then incentive will be 300 or else 200. Click on Ok to get the result.
- To see the difference of this Incentive column look at row number 7 where sales value is > 6500 and region is “South” so the Incentive value is 400.
- Once the changes are applied click on “Close and Apply” under the Home tab.
Now we have new columns with an older column in the Power BI Data Model.
Things to Remember
- IF is available as statement and DAX function in Power BI.
- In a statement, all the syntax should be in lower case characters, if any upper case included formula will show error and you can close out the formula.
This is a guide to Power BI IF Statement. Here we discuss how to use If statement in Power BI to arrive new column along with practical examples. You may also look at the following articles to learn more –
- Icons in Power BI Desktop
- How to Create Reports in Power BI?
- Power BI Tutorial for Beginners
- How to Use Calendar DAX Function in Power BI?
- How to Use Filter DAX Function in Power BI?
- Top Features of Power BI for Data Visualization
Purpose: to write IF ELSE and IF AND statements in Excel Power Queryand Power BI
Links to topics on this page
Power Query IF ELSE syntax
= if [condition] then [do this] else [do that]
Writing IF statements in Power Query is very similar to creating them in any other program, but one of the key things to remember is that the commands need to be in lower case, i.e.:
- ‘if … then … else’,
- not ‘IF … THEN … ELSE’.
If you’re getting an error, then it’s worth checking that first.
Another feature of IF statements in Power Query is that you don’t need round brackets ‘( )’ around the function commands; you do, however, need to have square brackets ‘[ ]’ around any column (variable) names.
If this is the first time you’ve worked in Power Query, see our post on how to connect to data and create a data query in Excel: Get started with Power Query – connect to data and add an Index
Create an IF ELSE statement in Power Query
- Either select an existing data query:
- select ‘‘, or ‘‘ from the Data tab of the ribbon,
- select a query, and
- right-click and select ‘‘;
- Or create a new data query from a data range or Excel table:
- highlight the range of cells, or click anywhere in the Excel table,
- select ‘‘ on the Data tab (or the equivalent in your version of Excel), and
- select ‘‘.
- ADD THE IF STATEMENT: On the ‘‘ tab of the Power Query Editor window, click on the ‘‘ icon.
- In the Custom Column editor window, give your new column a name, and enter your ‘if…then…else’ statement into the Custom column formula window. You’ve got your available columns in a pane on the right which you can double-click to add the item to your formula.
On the next page we give an example of a Power Query IF ELSE statement, and how to create a set of nested IF AND … ELSE statements in Power Query >>
- 2012 ford focus transmission module
- Where is poshmark headquarters located
- University of alabama honors dorm
- Augusta ranch houses for sale
- Are all kitchenaid attachments universal
- Fitzgerald subaru frederick, md
The IF function in Power Query is one of the most popular functions. It allows you to make comparisons between a value and what you’re looking for. You use it in conditional statements and it has two results. First, it determines whether a condition is met or not. Then, when the specified condition equals true, Power Query returns one result. And when it’s false it will return another. In this post, you will learn all about if statements in Power Query. I will cover its syntax, where to write them, example if statements and what errors may appear.
Table of contents
An IF statement is also referred to as a logical formula. The syntax of a basic IF function in Power Query is as follows:
All programming languages use IF statements, and they often look very similar. Yet the syntax may vary slightly.
1.1. Differences between Excel and Power Query
For example, the IF formula in Excel looks like:
The if formula in Power Query differs from Excel in three ways.
|IF Function in Power Query||IF Function in Excel|
|Has a lower case syntax. Power Query is case sensitive. Capitalizing any letter in the words if, then, and else throws an error.||Has an uppercase syntax. Yet Excel automatically converts lowercase to uppercase. And it does not throw an error.|
|Separates arguments by the lowercase words then and else.||Separates arguments by your system’s default separator. In most cases, this is a comma or semicolon.|
|The words if and else determine the beginning and end of an if function.||Arguments are always provided between parentheses. These show the start and end of the function.|
2. Conditional Column versus Custom Column
There are two easily accessible ways to add an if-statement. And you can find both in the Add Column tab in the Power Query ribbon. Using the user interface one could either add a ConditionalColumn or write it from scratch by adding a Custom Column.
2.1. Using a Conditional Column
Without any coding knowledge, the easiest way to add a conditional statement is by using a Conditional Column. You can find the button to create one in the Add Column tab. It allows you to create basic if-statements.
After clicking on Condition Column, the below menu opens:
You can use this menu to set up conditional logic. The different options are:
|New Column Name||Enter the name for your new column.|
|Column Name||The column to evaluate your if-condition against.|
|Operator||Operators are available depending on the data type of the Column Name.|
Text: begins with, does not begin with, equals, contains, etc.
Numbers: equals, does not equal, is bigger than or equal to, etc.
Date: is before, is after, is equal to, does not equal, etc.
|Value||You can enter a value, or select a column or parameter to compare your evaluation against. This value together with the Column Name and Operator makes up a condition.|
|Output||If the condition is met, what should it return? This can be a value, column value, or parameter.|
|Else||When the condition is false you can specify what to do. Options are similar to the Output field.|
Beginners may be happy with the conditional column using the User Interface (UI). For more complex expressions, however, you soon stumble upon the limitations of the UI. For example:
- adding complex if statements to test conditions that include multiple columns is not possible. This could involve operators like AND, NOT, and OR.
- evaluations can only be done with the operators provided in the default menu. Others (like Date.Year, Text.Start, Text.Proper, etc.) can’t be performed through the provided menu. You would need to add a helper column to make these comparisons.
- the result of a true or false expression can only be a hard-coded value, column value, or parameter. Returning a calculation that combines these three is not possible in the interface.
To address these limitations one could instead write a Custom Column.
2.2. Using a Custom Column
When you need more complex if-statements you can resort to the Custom Column. To create one you can click the Custom Column button found in the Add Column tab of the ribbon. This will open the custom column dialogue box.
The custom column formulas allow for more complexity. Here one can include combinations of hard-coded values, functions, columns, and parameters for both the if-condition and the true and false–expressions. More on example if-statements you will find below. You can paste these examples directly in the Custom column formula box.
3. If statement examples
You have now seen the theory. But to get the hang of it, I will provide you with several examples on how to write conditional if statements. Let’s imagine we’re looking at the following dataset. It shows the quantity sold of each order with the respective unit price.
3.1. A simple if statement
The column Package indicates the Quantity of each unit. It can refer to a single unit (each), two units (pair), or four units (packet). We can use the if function to create a column to show the number of items sold.
So without the batches. One could write:
3.2. Nested if statements
This is great, but it only shows numbers when the package is sold by unit. What if we would want the formula to include pairs, using an if statement with multiple conditions? We will need to write a nested if statement to achieve this.
The key to making this work is to put the second if statement after the first elseclause. In this example, I have used spacing and put the formula on two lines. They help readability and still perform correctly. This should make it even more clear.
You will soon get accustomed to the if then else construct used in Power Query. And with the knowledge you can easily combine multiple if functions to include batches of 4 in there as follows:
The result looks like the below picture.
Notice that you can add the before specified code in the CustomColumn box in the AddColumnribbon menu. The M-code in the formula bar also includes the relevant syntax for the Table.AddColumn function. Everything that comes after the word each is similar to the if-statement displayed earlier.
3.3. If statement with Common Operators
So far the conditions tested column values to be equal to a single value or a list of values. To make your conditions a bit more advanced you can use common operators. Common operators can be:
|<>||Is not equal to|
|>=||Bigger than or equal to|
|<=||Smaller than or equal to|
Applied to an if-statement, these operators could look like:
3.4. IF statement with OR Logic
Let’s imagine you want to add a column that specifies whether a line refers to a single unit of product or multiple. And you are given the following considerations:
- You should base your conditions on the Package column.
- The current column contains three unique values but in the future, this amount could change. Changes should not be considered.
- Any values that do not equal each, pair, or packet should return null.
To achieve this, you can add OR logic to your if statement. Write the word or in lowercase.
3.5. The IN function equivalent
When you need to check whether a column contains one of many values, it may be too arduous to add OR logic to your if statements. As an alternative one could provide the values to test as a list. Other programming languages often use the IN function for this. The equivalent of the IN function in Power Query uses List.Contains:
The function evaluates whether the list contains the value in the column Package. If the value appears, the expression returns true. This example only uses two values in its list. One could expand this list to as many as required though.
3.6. If statement with AND Logic
Your company gives discounts when you order at least 5 packets for a unit price of at least 200. To test this, your conditional statement needs to include two conditions. You can do that by adding AND logic to your statement, also written in lowercase:
This example only included a single and operator, but know that you could add more to the same expression.
3.7 If statement with NOT Logic
You may sometimes find the need to test whether something is not true. The not operator can help you out here. Let’s imagine we want to reverse the previous statement. You can add the word not right after the word if and make sure to put the entire if condition between parentheses.
If this caught you attention, you may find it useful to look into replacing values based on conditions. It’s a bit more complex, but strongly related to the if conditions.
4. Error messages
The differences between a conditional statement in Power Query and Excel are small but important. Especially since small mistakes easily cause errors in Power Query. And the error messages are often not very helpful.
4.1 Token Eof expected
For example, you should write the words if, then, and else in lowercase for a working formula. If you write any of these letters in uppercase in the Custom Column box, Power Query will throw the error. The below example shows the word IF capitalized and you can see the error message below.
Most users won’t know what “Token Eof expected” means. In this case, the message occurs when you write one of the earlier mentioned words in capital letters in the Custom Column box.
4.2 Expression.SyntaxError: Token Comma expected
A different error occurs when you edit your formula in the formula bar. Taking the same example as before, the capitalized IF word now results in a different error message.
The message “Expression.SyntaxError: Token Comma expected” can be confusing. From the first part, I deduct there is a Syntax Error. The second part interestingly suggests a missing comma is causing the error. And this is not the case here.
4.3 Expression.SyntaxError: Token Literal expected
When adding conditions to your formula that include words like NOT, AND, and OR, you may get another error. In the example below, you can see the word and that suggests another condition is coming. Yet no additional condition is written. The word elsefollows after and indicates the second argument of the function should begin. For as this an incorrect expression Power Query returns: “Expression.SyntaxError: Token Literal expected“. An error that is not very clarifying to the average user.
4.4 Expression.SyntaxError: Token Then/Else expected
Last but not least two other errors can occur in the following situation. In Power Query you can write if statements with the words then, and else to separate arguments. This means that when writing nested if statements, each of the statements needs to have a then and an else clause. If you omit these and replace them by a separator, you would get one of the following error messages:
Expression.SyntaxError: Token Then expected.
Expression.SyntaxError: Token Else expected.
These last two errors are a bit clearer, but can still confuse users. After all, what is a token?
In this article, I showed several examples of how one could leverage if-statements in Power Query. One thing we didn’t cover is creating conditional statements by writing custom M-code using the advanced editor. If you’re up for a challenge make sure to check out how to return values based on a condition. Mastering that skill will strongly improve the amount of data challenges you can tackle. That’s all I want to share about the if function in Power Query. See you next time!
About Rick de Groot
Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.
Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.