DAX Functions are an essential part of every Power BI training in Sydney and Melbourne because Power BI trainings are incomplete without DAX. Learning DAX is important for mastering Power BI, both at basic and advanced levels. We have started a series of DAX weekends along with Power BI blogs to help build a sound foundation of DAX alongside Power BI. In the last blog post, we discussed three logical functions in DAX, i.e. IF(), AND() and OR(). In this blog post we will walk through some other logical functions such as SWITCH(), TRUE(), FALSE(), NOT() and understand their syntax and usage. Logical functions in DAX work on an expression and return information about the expressions. Logical Functions are the functions that work with logical Values i.e. TRUE and FALSE. Logical functions evaluate logical operators such as less then (<), greater then (>) or equal (=) on a given expression. Functions that fall in this category include:
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.
Adventure Works Model Structure
The model contains seven tables:
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. So we begin by following the best practice of creating a Measure Table and then creating measures for each DAX expression. In this blog however we will be creating calculated columns instead of calculated measures. We begin by creating measures and then shifting to calculated columns.
SWITCH():
To create a measure:
1.Right on All Measures Table in the Fields Pan.
2.Click on New Measure.
3.Create a Measure with the name ‘Week Day’ that assigns a week day to each date value.
A single value for column ‘Date’ in table ‘Date’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
4.Right on Date Table in the Fields Pan.
5.Click on New Column.
6.Type the Following DAX expression to create a calculated column:
A new column will be added to the Products table and can be recognized by the symbol for the calculated columns.
Lets check the values in this newly created column.
7.Click on Data in the left Ribbon.
8.Select Date Table from the Fields Pan.
Notice that weekdays have been correctly assigned for each date.
SWITCH vs. IF
IF() is used when there is a single True value and a single Alternative value. SWITCH is used when the evaluated expression is tested for multiple results and alternate values. Although multiple nested IF() can achieve the same result as SWITCH(), SWITCH() is preferred because SWITCH expressions are easier to write and lesser prone to error. A large number if IF() calls have a negative impact on the performance of dashboard. For details on IF() please visit: https://powerbitraining.com.au/logical-functions-in-dax-if-and-or/
TRUE():
FALSE():
1.Right on All Measures Table in the Fields Pan.
2.Click on New Measure.
3.Create a Measure with the name ‘Total Sales’.
If you have been following our DAX weekend series, you will already have this measure created. For details regarding SUM function please visit: https://powerbitraining.com.au/power-bi-dax-functions-sum-count-and-distinctcount/
4.Right on All Measures Table in the Fields Pan.
5.Click on New Measure.
6.Create a Measure with the name ‘Grand Total’.
Now lets visualize this,
7.Click on Table visual in the Visualization Pane.
8.Drag and Drop the Month Column from the Date table to the Values.
9.Drag and Drop Grand Total From All Measures to the Values.
TRUE and FALSE serve as constants. The total sales for each month is calculated by the measure Total Sales and when the Total Sales is greater than 20000, TRUE is returned otherwise FALSE is returned. Notice that the incoming filter context plays an important part here as the aggregated value for all entries for a month is calculated.
NOT():
1.Right on All Measures Table in the Fields Pan.
2.Click on New Measure.
3.Create a Measure with the name ‘Not Achieving’.
4.Click on Table visual in the Visualization
5.Drag and Drop the Month Column from the Date table to the Values.
6.Drag and Drop Grand Total from All Measures to the Values.
7.Drag and Drop Not Achieving from All Measures to the Values.
Conclusion:
This blog post serves in continuation to the last blog where IF, AND and OR were discussed. In this blog post, SWITCH, TRUE, FALSE and NOT have been discussed. All these functions fall in the category of logical functions in DAX. SWITCH serves as function with multiple IF ELSE values evaluated for an expression. TRUE and FALSE are logical value and always return a constant. NOT operator returns the logical opposite of TRUE or FALSE. This blog post briefly discussed all these functions with examples.
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:
