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.
IF()
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 ‘Price Group’ that categorizes the List Price values into two categories i.e., Low and High based on the Condition ‘Product'[List Price] < 500.
Notice that a measure cannot be created in this case because a single value for column ‘List Price’ in table ‘Product’ 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. Simply said, ‘Product'[List Price] must be wrapped inside another function for valid measure. There are many possible solutions to this, for new we create a calculated column instead. To create a calculated column:
1.Right on Products Table in the Fields Pan.
2.Click on New Column.
3.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.
4.Click on Data in the left Ribbon.
5.Select Product Table from the Fields Pan.
Notice that a value has been assigned to each Row in the ‘Price Group’ column. This creates a ROW Context.
AND()
Logically, the results for AND expressions are:
The AND function returns TRUE only if both the conditions provided as arguments to the parameter of the function are TRUE.
To see how AND can be used for data wrangling, lets create a calculated column using AND.
To create a calculated column:
1.Right on Products Table in the Fields Pan.
2.Click on New Column.
3.Type the Following DAX expression to create a calculated column:
This DAX expression Categorizes the products based on the list price using 2 conditions.
Condition 1: ‘Product'[List Price] > 500
Condition 2: ‘Product'[List Price] < 1000
Lets check the values in this newly created column.
4.Click on Data in the left Ribbon.
5.Select Product Table from the Fields Pan.
The product whose List price complies with both the conditions simultaneously are categorized as Medium.
OR()
Logically, the results for OR expressions are:
The OR function returns FALSE only if both the conditions provided as arguments to the parameter of the function are FALSE.
To see how OR can be used for data wrangling, lets create a calculated column using OR
To create a calculated column:
1.Right on Products Table in the Fields Pan.
2.Click on New Column.
3.Type the Following DAX expression to create a calculated column:
This DAX expression Categorizes the products based on the list price using 2 conditions.
Condition 1: ‘Product'[List Price] < 500
Condition 2: ‘Product'[List Price] > 1000
Lets check the values in this newly created column.
4.Click on Data in the left Ribbon.
5.Select Product Table from the Fields Pan.
The values in the Price Group column categorize the products based on the values for the List Price. The values below or above certain thresholds are considered ‘Too High/Too Low’
Conclusion:
Logical functions in DAX are used to perform logical calculations. This means evaluating the data values using logical operators such as <, >, = , etc. IF() function is used to evaluate an expression and perform an operation based on the result of the evaluation. AND() function is used for logical multiplication and returns TRUE only if both of the arguments are TRUE. OR() function is used for logical addition and returns TRUE only if any of the arguments is TRUE.
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:
