DAX – CALCULATED COLUMN

What is a calculated column in DAX?

This is a user-friendly option to add new, formula-based columns to a table in powerBi model if you want to actually see the result of the table.

With that being an advantage, it also has few restrictions to be noted as below:

1. Calculated columns refer to entire tables or columns but not a A1 style cell reference.

- Meaning you can’t refer them to a particular cell like D15 or D7

2. Calculated columns generate new values for each row which are visible only in the data view.

3. Calculated columns understand row context.

- Meaning they can see the information contained in each row in which they calculate : which makes it is great for defining aspects like properties based on information within that row but it is not helpful in aggregating functions like sum, count, etc. In other hand Measures are meant for handling aggregate functions.

Where can it be most helpful?

1. Use calculated columns if you want to stamp static fixed values on each row within a table.

2. Use calculated columns for filtering data.

Let’s see with an example:

Step 1:

Open PowerBi desktop. You will have 2 main tabs Home and Open as shown in the picture.

Select “NEW” report present to the right of “Home” tab.

Step 2:

Select “GET DATA “ in the HOME tab.

You can see a drop-down menu having all options available for data source.

Select “Excel workbook”

Folder from your system pops up. Now select the excel file you want to add by navigating through the folders you have in your system.

A “Navigator” pops up as shown in the picture:

From which select the table you want to perform calculated column. In my case, the table I want to load is blog-calculated filed. So, after selecting that, click “Load” tab present at the bottom right corner.

Step 3:

Select table view on the left panel.

Step 4:

The third column in the table shows the lab test results for checking Hemoglobin level in a diabetes patient. We are now going to add a calculated column that will display the value in text format showing whether the patient is anemic or non-anemic.

This is calculated based on the Hgb values in the third column.

The condition is to check if Hgb > 12.5 – Non-anemic and if Hgb < 12.5 – Anemic.

Select “Table tools” next to help tab and then select “New Column”.

Step 5:

Let’s name the new column “Anemic_condition”.

We are going to use if condition. As soon as you type ‘if’, we get different parts of our if statement. Its easy to follow it and apply our condition.

Our if condition is: (Use shift + enter to return down a line)

Anemic_condition = IF

('blog-calculatedfiled'[Hgb g/dL] > 12.5,

"Non-anemic",

"Anemic"

)

Once you write the condition, hit enter. You can see your new calculated column that says whether the patient is Anemic or not.

Now select File from top left corner and save your work by giving an appropriate name to it.

So, the key take away from this write up is calculated fields can be used for filtering data and to stamp static fixed values on each row. It can never be used for aggregate functions.