Power BI Intermediate Level: 47 - CALCULATE Mastery - Using Measures in Calculated Columns

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: You can use DAX measures in DAX calculated columns to add summarizations in dimension tables. Use the CALCULATE function in a calculated column to make sure that the related table which you summarize gets filtered according to each corresponding row of the calculated column. Calculated columns are static and do not react to user filtering.

As mentioned in the previous article, a major use case for calculated columns in DAX is to add summarizations in dimension tables about a related fact table.

For example, in the 47_DIM_Stores table, we may want a calculated column with the number of transactions from the FACT_Transactions table.

Calculated Columns without Using CALCULATE

Select the 47_DIM_Stores table, click New column, and add the following formula:

# Transactions without CALCULATE = COUNTROWS('46_FACT_Transactions')        

The result may not what you would expect. In each row, we get the grand total of transactions over all stores. But our intention is to get the number of transactions belonging to each store.

Calculated Columns Using CALCULATE

How do we tell Power BI that we want to transition the row context (store) of 47_DIM_Stores table to a filter context (filter by store) of the related 46_FACT_Transactions table? This is done by wrapping the calculation in a CALCULATE function:

# Transactions with CALCULATE = 
CALCULATE(
    COUNTROWS('46_FACT_Transactions')
)        

Now we are getting the desired result.

Whenever you get the same value in every row of a calculated column, you are most likely missing a CALCULATE or haven't properly defined the table relationships.

Calculated Columns Using a Custom Measure

Let us try one more thing. Let us define the number of transactions as a measure

47 # Transactions = COUNTROWS('46_FACT_Transactions')        

and use this measure in a calculated column:

# Transactions with Measure = [47 # Transactions]        

Why are we getting the correct result here even though we are not using CALCULATE? The reason is that when using a custom measure, Power BI always implicitly raps that custom measure inside a CALCULATE function. This is because Power BI assumes that when we use a measure in a visual, we want the data to filter based on the data points in the visual, and not the grand total for every data point.

Calculated Columns Using CALCULATETABLE

There is a function called CALCULATETABLE, which performs the table-equivalent of CALCULATE. For clarification:

• CALCULATE performs row context to filter context transition and returns a single value which is calculated on a filtered table
• CALCULATE table also performs row context to filter context transition but returns a filtered table

Before we wrapped COUNTROWS inside CALCULATE like this:

# Transactions with CALCULATE = 
CALCULATE(
    COUNTROWS('46_FACT_Transactions')
)        

Alternatively, we could get the filtered table with CALCULATETABLE and perform COUNTROWS on that filtered table.

# Transactions with CALCULATETABLE = 
COUNTROWS(
    CALCULATETABLE('46_FACT_Transactions')
)        

There is also the function RELATEDTABLE, which is just an alias to CALCULATETABLE, because some people find the name RELATEDTALBE more intuitive. Either way, we get the filtered table with the related records. Since a measure or a calculated column both return a single value (per row), you cannot output a table but need to summarize it, for example here by using the COUNTROWS function.

Important Restriction

When you use calculated columns in DAX, they are static like calculated columns which you create in Power Query, meaning that they only get updated when the report gets refreshed or when you change the formula for example. However when a user interacts with the report and performs filtering, the values in calculated DAX columns stay the same, even when using a measure inside the calculated column. If you want fully dynamic and interactive results, you must use measures!

See the below example where we have a table visual with the calculated columns on top and a table visual with the 47 # Transactions measure on the bottom. When filtering for different menu items, you see that only the measure adapts to the changes, whereas the calculated columns are static.

Conclusion

Now you know that calculated columns, whether created in Power Query or in DAX, are static and should be viewed as such. In order for your report to be fully dynamic with regards to user filtering, you must measures in visuals, not calculated columns. I recommend the following:

• Create calculated columns in Power Query as much as possible to have it together with your other data preprocessing. And that is how you should view it, it is simply prepressing of data.
• For your dynamic visuals, use measures directly, not calculated columns based on measures, because they don't react to user filtering.
• Use DAX calculated columns only in special cases where they would be difficult or impossible to implement in Power Query. This is usually the case when you can leverage table relationships, DAX functions, or measures.

In the next article, let's create a custom calendar table to prepare our exciting journey into time intelligence in Power BI!

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: Creating a Custom Calendar