Source: https://docs.microsoft.com/en-us/dax/logical-functions-dax
The data set used for this blog post is Adventure Works DW 2020. It is a sample data model developed by Microsoft to enhance the DAX learning of their users. The Adventure Work dataset represents the data of fictitious bicycle manufacturer that sells bicycles and accessories to global markets.
Source: https://docs.microsoft.com/en-us/power-bi/guidance/dax-sample-model
This is what your data model looks like once you have loaded the Adventure Works data set into Power BI desktop.
The model does not contain any DAX calculations. In this blog, we will be creating calculated columns instead of calculated measures.
To learn more about creating calculated columns and calculated measures visit: https://powerbitraining.com.au/calculated-column-and-measures-in-power-bi-using-dax/
COALESCE()
COALESCE() is a relatively new function in DAX and was introduced in March 2020. This function returns the first non-blank value. Therefore, it is particularly useful for converting Blank sales value to zero. For this blog post, we have preprocessed the data set by creating a Net Price column containing both blank and non-blank values.
We will first create a column using IF() and then create the same column using COALESCE().
To create a Calculated column:
1.Right on Sales Table in the Fields Pan.
2.Click on New Column.
3.Create a Column with the name ‘Price’.
The Price column is created by analyzing the value of Net Price. If the Net Price does not contain any value, then the Unit Price is taken as the Price. Now let’s create a column using COALESCE () which the same functionality.
4.Right on Sales Table in the Fields Pan.
5.Click on New Column.
6.Create a column with the name ‘Price with COALESCE’.
Now let’s verify the values in the both columns
7.Click on Data tab in the left Ribbon.
Both the newly created columns contain the same value.
IFERROR()
IFERROR function is used to handle ERRORS. If VALUE or Value_If_Error parameters do not contain any value then the function takes “ ” (empty string) as the default value. For this part of blog post, there are some errors intentionally introduced to the Net Price column.
Lets create a column using IF() and then then IFERROR():
1.Right on Sales Table in the Fields Pan.
2.Click on New Column.
3.Create a Measure with the name ‘Price’.
Now let’s create another column using IFERROR( ).
4.Right on Sales Table in the Fields Pan.
5.Click on New Column.
6.Create a column with the name ‘Price with IFERROR’.
Now let’s verify the values in the both columns
7.Click on Data tab in the left Ribbon.
Difference between IF() and IFERROR()
IFERROR is closely related to the IF function. IFERROR is basically a combination of IF and ISERROR functions.
IFERROR(Val/Exp1, Val/Exp2) := IF(ISERROR(Val/Exp1), Val/Exp2, Val/Exp1)
IF formula evaluates a given expression and takes three arguments: What to evaluate? What to do if the expression returns true? What to do if the expression returns False?. IFERROR on the other hand takes 2 expressions. IFERROR is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
CONCLUSION:
Logical functions in DAX are used to perform logical calculations. IF() is the most commonly used function from the list of logical functions available in DAX. In this blogpost, two functions which perform the same calculations as IF() using fewer number of arguments are introduced.
Are you a data analyst and want to learn more about Power BI?
Why not sign up for Power BI training in Australia. We provide our services in the following regions:
