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!