DAX Calculated Column with IF Statement

Welcome to Excel Jet Consult blog post. In this tutorial, we will look at writing IF Statement to create DAX Calculated Column in Excel. Let’s get started

Video

You can watch the video below. Subscribe, comment and give likes

Sample Data

In the caption below, we have our data formatted as an Excel Table using CTRL + T and already loaded to the Data Model (Power Pivot)

DAX Calculated Column

The DAX Calculated Columns literally defines the values in column by combining values from two or more columns. Calculated Columns can be used in charts and PivotTable Reports

IF Statement

The IF Function checks whether a condition is satisfied and returns one value if the condition is TRUE and another value if the condition is FALSE

IF Function Syntax         =IF(logical_test, [value_if_true], [value_if_false])

IF Statement implies combining or nesting two or more IF function

Question to Solve

Based on the caption below, we have units column that has values ranging from 7 to 200. We want to classify the units in a new column as follows:

If the units is less than or equal to 50, return “Bronze”

If the units is less than or equal to 100, return “Silver”

If the units is less than or equal to 150, return “Gold”

If the units is less than or equal to 200, return “Platinum”

Solution

To create the calculated column,

  • Double-click on Add Column and provide descriptive column name. In this example, we provided Customer Category
  • Hit Enter on the keyboard
  • In the Data Model formula bar, enter the DAX formula:

=IF(Sales_Data[Units]<=50,“Bronze”,IF(Sales_Data[Units]<=100,“Silver”,IF(Sales_Data[Units]<=150,“Gold”,“Platinum”)))

  • Click Enter

Sales_Data refers to the Table name. Units inside square brackets is the column name. Then, we used less than or equal to operators.

The result gets filled down the entire column based on the logical test

From the caption below, we can see that the records in row one returned Bronze Customer Category because the units is a value between 1 to 50. The row 15 returned Platinum customer category because the units is 162 which falls between 151 to 200.

We can go ahead to use the newly created calculated column in our reports. Hope you enjoy this tutorial. See you in the next tutorial. Stay Safe and always use your nose mask!

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *