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 to use calculated columns and measures?

Often, we get confused between measures and calculated columns. The reason is both uses DAX expressions. Context of evaluation to apply what in which situation makes the difference. A measure is where a cell is evaluated in a report or query whereas if you evaluate at the row level, it is a calculated column. For example, if sum is used in measure, it means the sum of all the cells which will be aggregated to one cell whereas in calculated column, the value of the column in the current row is alone aggregated. The most important point to be noted is that a measure needs to be defined in a table though the measure does not belong to the table. It gives the flexibility to move the measure from one table to another without affecting its functionality.

When we refresh the data, the value of a calculated column is computed, and the current row is taken as a context. This doesn’t require user interaction in the report. When we apply a filter such as slicer, a measure’s current context depends on such filters applied as it operates on aggregates.

When to use measures?

· Use measures to aggregate.

· Measures are used to numerical calculated values that can be analysed within the value fields of the report visual.

· Measures can be used either by implicit or explicit method which will be explained in detail below.

When to use calculated columns?

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. This will take you to a new page for adding your new dataset.

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”.(We have so many options to upload data from various source and various format. In this example, we will be using excel dtaset. So, we will se how that can be done.)

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.( Sometime we save the datset in csv and try to open in excel format. Thats when you will not find the file when trying to upload from your desktop)

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. If you want to directly transform the data, you can select the next option near "Load" that says "Transform Data".

Step 3:

Select table view on the left panel. In coming posts, we will explore more on other views like modeling.

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.