The logical functions in Power BI are essential while writing DAX expressions. Logical functions help us in decision making to check if any condition is true or false.
Once the data has been extracted through Power Query, these DAX expressions help us to fetch important information from the data. Here is an article explaining the difference between Power Query and DAX, which you may be interested in.
The logical functions in Power BI I will discuss here are IF, AND, OR, NOT, IN and IFERROR. They are all true to their names and do the task exactly as they are used in English.
I will discuss them along with their application on a data set containing the area and production of different crops of different Indian states. Below is a glimpse of the dataset.
I have collected the data from web with the data scraping feature of Power BI. Here is the article where I have explained how you can take advantage of this nifty feature of loading data from the web in Power BI.
“IF” logical function
The IF function accepts three arguments. The expression of this logical function is as below. We can see that it has the same English conditional context and very easy to understand.
IF (expression, True_Info, False_Info)The first argument of this function is a Boolean expression. If this expression has some positive value the IF function returns the second argument otherwise the third argument.
Let’s see a practical example of its use on the India_statewise_crop_production dataset. I have created a new column Production_category using the IF function. If the production is less than 10, then it is under LOW production_category; otherwise HIGH production_category.
“Nested IF” function
We can use IF within another IF function, which is called the nested IF function. It helps us to check more than one condition at a time.
For example, I have placed two conditions here. One is the earlier one I used in the IF function and added another that if Production is greater than 500 then the production is HIGH else MEDIUM.
See the result below, how the Production_category column has the new values according to the NESTED IF condition.
“AND” logical function
It can take two arguments. If both the arguments are correct, it returns TRUE else FALSE. Its syntax is as below:
AND (Logical_condition1, Logical_condition2)I have applied the AND function to find out if the productivity is high or low. I have used AND to check if the conditions Area is less than 10 and production is higher than 200. If both the conditions are TRUE then it returns “High Productivity” else “Low Productivity”.
“OR” logical function
Unlike AND logical function, in the case of OR function if anyone condition holds true, the function returns TRUE. It returns FALSE only if both of the conditions are FALSE.
For the crop production data set, I have applied the OR function to check if both the conditions that are Area<10 and Production<20 are true then it should return “Low production” else “High Production”.
“NOT” function
The NOT logical function simply changes FALSE to TRUE and TRUE to FALSE. It is very simple to use. See the below example.
I have used NOT with the IF function. If the IF checks the condition Season=” Kharif”, if it is true, IF returns True, again the NOT function turns it to False. See the output column “Kharif_check”, it has False corresponding to Kharif and for other entries it has True.
“IN” logical function
The IN function lets us check the specific entries under a column and calculate corresponding values for other columns.
In this example, I wanted to calculate the total production for only three states “Assam”, “Bihar” and “Uttar Pradesh”. In order to do that, I have created one measure using the SUM and IN function nested under the CALCULATE function. And see the result on a card.
“IF ERROR” logical function
The IF ERROR is another very useful logical function that checks for any error and returns values accordingly. This function is very useful while checking arithmetic overflow or any other kind of errors.
The syntax for this function is as below:
IFERROR (Value, ValueIfError )You can get the syntax guide when you will select the function in the Power BI editor, see in the below image. As soon as I have started to type the function name, Power BI IntelliSense guided me with the autocomplete and the syntax for the function.
In my example, I have checked if there is an error in the Crop column. In case of any error found it should return “Error”. As there was no such error in the column so the IFERROR column has the exact values as in the Crop column.
