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.
The IF function in DAX
- If functions are often used to create categories within your dataset
- The syntax of the IF function is exactly the same in DAX as it is in Excel
- Specify the condition to be tested, the result if the condition is True, and the result if the condition is false.
- If there are more than two possible results, you need to nest IF statements inside each other
- Alt H, PT, C – Create Pivot Chart (from Power Pivot window)
- Alt JC, C – Change chart type
- Alt JT, SF – Add slicer
Logical functions allow us to test whether one or more conditions are true or false. Common logical functions in DAX include if, and, not, and/or, and they work in the same way as Excel's logical functions.
In this lesson we'll use an if function to categorize our pharmaceutical products by sales. This is a task we can actually accomplish using a calculated column or measure.
If we use a calculated column, the categories will be static and will not change at all. If we use a measure, the categories will by dynamic and a single product may have different categories depending on any slicers or filters in use.
In this instance we want the categories to remain the same so we'll use a calculated column. As the category is an attribute of each product, we'll put it in a product details table.
To that end we'll start the lesson with a product details table and data view. First, we'll create a column to calculate total sales for each product and call it product sales.
We'll enter a calculate function and calculate the sum of sales.
We want to extract sales figures from the pharma data table that relate to any specific product. We can do this using the function related table, where the pharma data table is the argument.
This function will use the relationship between tables in the data model to find only the sales for the specific product at each row of the product details table.
Next, we'll create a column called product size.
This will contain an if statement which will use our total sales column.
The first argument in an if statement is a logical test which is an expression that is either true, or false. The second and third arguments represent the value of product size if this statement is true, or false respectively. We'll classify any product that has total sales below 20,000 as small, therefore our first argument will be product sales, less than 20,000, and our second argument will be small in quotes.
Products with sales above 20,000 could be classified as either medium, or large so we'll add a second if statement.
If product sales are less than 50,000 the product size will be medium, otherwise it will be large.
This is a nested if statement, where one if statement contains another if statement. You will often use nested if statements, when you want to test multiple conditions. We'll close out the statements, and press Enter, and see each product now has a classification of small, medium, or large depending on its product sales figure.
Note that we did not have to create two new columns here. We could have calculated product sales as part of the if statement rather than putting it in its own column. However, this would have made the if statement more complicated, given that this table only has 20 rows, the computing inefficiency of adding an extra column is not a big issue.
Let's create a Pivot Chart to get a visual representation of product size.
We'll add it to a new sheet, expand the pharma data table and add product name, and sales.
We'll change the chart type from column to bar, sort the data from smallest to largest, and expand the chart so that we can see all the bars.
We'll then add the new product size field from the product details table as a legend.
It's clear that each product is classified by its total sales, we'll now add a slicer with a keyboard shortcut Alt, JT, SF. We'll navigate to the pharma data table, and select the date year field as the slicer.
When we select a specific year, products do not change their categories.
For example, in 2017, some small companies have higher sales than medium companies.
We created these categories using a calculated column and as a result the category for each product is based on its sales for the entire period from 2013 through 2017.
As a result, when we analyze just a small portion of that time period, the categories can appear to be wrong. Let's stop the lesson here.
As we've seen here, if statements can allow you to create categorical variables in your data set.