There are tons of options in Power BI to add your custom logic. Varying from SQL expressions at data ingest all the way up to Visual Calculations to add logic on a visual level. But how do they compare to each other, where should you do what?

As I’ve been a fan of making DAX easier since day one, I thought it may be a good moment to highlight the differences between calculation options using DAX in Power BI, like measures and calculated columns and how that is significantly different with Power BI Visual Calculations and how those can make your DAX easier.

What calculation options do we have?

In Power BI Semantic Models (or datasets if you will) there are many different options to do calculations or any sort of logic. Below a short list of options, starting as far upstream as possible, ending as close to the visual engine as you can get.

• SQL Query, I don’t come across this scenario that often, but it is completely possible to define some calculations in a SQL query which you execute as part of your data ingest in the Power Query editor. With that, you can pre-calculate values before the data even reaches Power BI as these queries will be executed on the source side. For example, you could multiply amount by price to get the total sales order value in a single column.
• Power Query, If you’re not that familiar with SQL or try to put all logic in one single place, Power Query will be another option to prepare your data. Just like the SQL Query option, the Power Query option will be a way to prepare and transform your data before the real data modeling starts. Similar type of calculations and complexity is possible as the SQL Query. Potentially the query might be executed on source side, if query folding is applied.
• Calculated Table, once the data is loaded to the model, the SQL and Power Query options are out of scope. A calculated table uses DAX as query language and allows you to add additional logic like calculations, even concepts like joins, to produce and materialize another table in your Semantic Model. Often, using calculated tables is less optimal than pushing transformations further upstream. Sometimes, a virtual calculated table can be used inside calculated columns and measures to produce results, but in that case the table will not be materialized to the model.
• Calculated Column, this option adds another column to existing tables and runs on DAX as query language. A calculated column is materialized to the model in all scenarios and therefore consumes memory. Ideally, calculated columns are pushed further upstream to any of the prior mentioned options and as far upstream as possible. Calculated columns work on row-by-row bases and calculates in row-context.
• Measure, this is the most common way to add (business) logic to your semantic model. It runs solely on DAX and is calculated on the fly in query context. Therefore, it consumes CPU, rather than memory. Typically, measures are used to aggregate values, like taking the sum or average of a column. Concepts like filter context and row context are applicable to measures to understand the internals of DAX measures, how they work and predict the outcome.
• Visual Calculations, the last and most recently added option that was introduced in February 2024 in preview in Power BI. Visual calculations are running solely on a visual level, compared to all other calculation options that run either on model level or even on source level. Just like measures, visual calculations are consuming CPU and are not materialized in the model. Also, visual calculations only understand the context of a visual and cannot refer to any column or measure that is not part of the visual. To understand the calculations made with visual calculations, you have to grasp the concept of the visual matrix, in which each visual can be translated back to a matrix visual. Simple things to do with a visual calculation is calculating a running sum for example, which is much easier than the required complex logic in measures or any other mentioned calculation option.

Dynamic calculations

Some of the calculation options discussed before, are materialized to the semantic model. This means as much as they are only calculated during the refresh of the semantic model and consume memory. But there are scenarios in which you want your calculations to be dynamic, maybe even with What-if parameters combined where users can even influence the different parameters and circumstances a value is calculated in.

In dynamic scenarios, there is a limited set of options you will have. Only measures and visual calculations are calculated in at runtime in CPU at runtime, and therefore dynamic.

What should you calculate where?

In some cases, engineers tend to pre-calculate everything upstream in a data platform. In principle, I do not disagree with them. Pushing heavy loading as far upstream as possible is a best practice. However, when it comes to aggregating data, this should be done in CPU if you ask me. Especially since the result of an aggregation can be dependent on row-level-security.

Following an example in which you want to calculate the average price for products in a given product category, let’s say bikes, this is something you should not pre-calculate if you ask me. This can perfectly handled by engine in Power BI. Another benefit is that having less columns reduces the model size in case of import models. Although integers (numbers) compress really well in the vertipaq engine (engine behind Power BI) each column you add to your model, consumes memory in the end.

Optimizing performance and reducing complexity

Running more complex calculations can impact performance massively. Let’s say, you run some complex DAX in which you pre-calculate a few variables with each their complexity, after which you want to aggregate these and then compare against each other.

Let’s say, rank the average price differentiation in percentages between list price and selling price over multiple years per product category. The goal of this calculation is to create a ranking over your product categories in which the number 1 will have the highest discount compared to lower numbers. To calculate this, you have to loop through each individual sales transaction in your multi-million record fact table, to calculate the price difference, convert it into a percentage and then roll-up to product categories. This can be a very heavy operation for the engine, let alone writing a measure like this is very complex.

Visual Calculations could be a great option to ease this calculation. It is much easier to split the complexity in multiple sub-calculations instead of trying to understand the row- and filter context, as well as context transition in measures like these. Also, you will be highly benefitting from the way how Visual Calculations are computed in the engine. Instead of looping through the millions of records, visual calculation cannot reach out back to the model, but will only calculate based on sub-results as presented in the visual. Therefore, the aggregation of many records only happens only once.

Validating outcomes when calculations are done with visual calculation sis also much easier. Especially since visual calculations follows a “What You See is What You Get” (WYSIWYG) experience and you can simply validate that A + B must lead to C.

Wrap up

In this blog, I aimed to highlight the different places in which you can run calculations in Power BI semantic models. Also, I aimed to highlight a bit more where visual calculations will come in in which you will benefit from A) better performance, B) reduced complexity and C) easier validation.

I personally think, visual calculations are highly underappreciated at this point and should be used way more often. Although, a big down-side could be missing a re-usability story around visual calculations. Though, the complex setup I used as example above, is in my opinion a typical one that you may not need to use in every other report directly. Also, discoverability of visual calculations is still a challenge, where definitions may become fluid and up for discussion.