Sign in or start a free trial to avail of this feature.
6. Introduction to Logical Functions
Logical functions, such as IF statements, allow you to test conditions. DAX’s conditional functions work very similarly to those of Excel, as we will see in this lesson.
Understanding Logical Functions (00:08)
Logical functions allow us to test if conditions are true or false. Common logical functions in DAX include IF, AND, OR, and NOT. Logical function are often used to create categories. For example, we use an IF statement to categorize pharmaceutical products based on their total sales.
Lesson Goal (00:37)
The goal of this lesson is to use the IF function to categorize our pharmaceutical products by their total sales.
Understanding Categories (00:47)
In DAX, we can create categories using a column or a measure. If we use a column, then the category values will remain fixed. If we use a measure, then the category values will be dynamic, and will adjust when we apply filters and slicers. In our case, we create a column to represent the category for each product.
Calculating Total Sales for Each Product (01:22)
In order to classify products by total sales, our product details table needs a column containing the total sales for each individual product. We create this column using CALCULATE. The expression to calculate is the sum of sales. We then use the function RELATEDTABLE to filter the transactions table to show only transactions for an individual product.
Creating a Nested IF Statement (02:13)
We then use the IF function to categorize each product based on its value in the total sales column. An IF function takes three arguments. First is the logical condition, which is an expression that is either true or false. Second and third are the values to return if the condition is true or false respectively.
The return values of an IF function can include another IF function. An IF function contained in another IF function is called a nested IF function. Nested IF functions let us create an IF statement with more than two possible values. For example, we use a nested IF function to categorize products as small, medium, or large, based on their total sales values.
Using the Categorical Field (04:45)
Once we create a new column with an IF function, we can use the new column in visualizations. In our example, we used a column to categorize the products. This means that the category for each product remains fixed even if we add a slicer to the report page, and use it to filter the chart.
Over the next two lessons, we'll look at logical functions.
Logical functions allow us to test whether one or more conditions are true or false.
Common logical functions in Dax include If, And, Or, and Not.
They can be useful in a variety of situations, such as creating categories.
Our pharmaceutical company wants to create three different marketing campaigns based on product revenue.
They believe that products with high revenue should be marketed differently than products generating low revenue.
In this lesson, we use the If function to categorize our pharmaceutical products by sales and help the company plan their marketing. We can accomplish this task by creating a column or measure.
In making this decision, we need to consider whether we want the categories to be static, where a product has one category at all times, or dynamic, where a product category can change according to filters and slicers.
In this instance, we want the categories to remain the same. So we'll create a column. As the category is an attribute of each product, we'll put it in the product details table.
We'll go to data view and navigate to the product details table. Before creating our category column, we need to create a column calculating total sales for each product.
We'll create a new column and call it product sales.
We'll then enter the calculate function, sum the sales, and use the related table function to filter our data set. This related table function will filter the pharma data table to return only transactions related to the specific product in the product name column. The sum function then calculates a sum of all sales for that product. Don't worry if you're having trouble understanding the related table function. We'll cover it in greater detail in a later lesson. We'll press enter to create the column.
Each row now shows total sales for the product in question.
Next, we'll create a column called product size to classify each product as small, medium, or large based on total sales.
We'll use an If statement to assign the correct classifier to each product.
In order to save time, I'm going to paste in the formula and then explain what it means.
The If statement starts with a logical test, which is an expression that is either true or false.
The second and third statements represent the value of product size if the statement is true or false respectively.
Therefore, if a product has total sales below 20,000, it will be small.
If sales are above 20,000, it could be either medium or large, which is where the second If statement comes in.
If sales are between 20,000 and 50,000, the product size will be medium. Otherwise, it will be large.
This is a nested If statement. That is, where one If statement contains another If statement.
Nested If statements are one option for testing multiple conditions in Dax.
Note that we could have calculated product sales as part of the If statement instead of putting it in its own column. I chose not to do this in order to keep the If statement shorter and simpler. The structure has a slight performance impact on the data model. But given that the table only has 20 rows, this impact is insignificant.
We'll press enter and see that each product is now classified as either small, medium, or large.
Let's move to report view and see a visual representation of this information.
On a new page, we'll create a clustered bar chart of sales by product.
We'll then add the new product size column as a legend.
We can see very clearly that each product is categorized by its total sales.
Note that the categories are fixed since we created this field as a column.
To demonstrate this, we'll add a slicer to the canvas and then add the date field to the slicer.
We'll then set the date range from January 1st, 2017, to September 1st, 2017.
We can see that the categories do not change.
As a result, some small products have higher sales than some medium products.
This is because the categories reflect total sales over the entire data set, while our chart only reflects data from 2017.
Let's stop the lesson here. We've seen how to use If statements to create categorical variables in our data set.
In the next lesson, we'll learn how to create more complex conditions using the And and Or functions.