Apr 28

Performance Comparison of Power BI Visual Calculations

Updated: Jul 19

Visual Calculations are a new feature bundled with the March 2024 release of Power BI. While it's still a preview feature as of this time, the temptations are too juicy to resist.

I will not go into detail about what Visual Calculations are in the article. If you do not know what these are, please refer to the Microsoft documentation here: Understanding Visual Calculations

Performance Comparison of Power BI Visual Calculations

Visual Calculations propose an enticing performance benefit; one that can potentially improve loading speeds drastically. In Microsoft's own words:

Compared to measures, visual calculations operate on aggregated data instead of the detail level, often leading to performance benefits. When a calculation can be achieved either by a new measure or a visual calculation, the latter often leads to better performance.

Source: Using visual calculations in Power BI

Is this really true...? I was curious about this claim, so I had to do some testing on my own. Here is a summary of my findings:

Visual Calculations are much faster when used to replace a complex measure, particularly over a large model.
Visual Calculations are also technically faster even with simple measures, but only by 20 - 40 ms.
If you use hierarchies in the axis, Visual Calculations may actually be slower by about 200 ms.

In a side by side comparison, we see that complex measures reach above 1500 ms in the largest model, whereas visual calculations run in just over 100 ms for the same dataset - effectively reaching a 90% reduction in loading times.

In the following charts, I separated the measure type (simple and complex) into two charts, keeping the type (measure and visual calculation) on the legend. Here we can see the drastic performance difference of using a complex measure (left). On the right, we can see that simple measures offer relatively small performance benefits.

Test Parameters

For this analysis, I created one needlessly complex measure (Accumulated Distinct Count), and one simple measure (YTD Sum):

Complex Measure

Accumulated Distinct Count of Products = 
	VAR _MaxDay = MAX( 'Date Table'[Date])
	RETURN
	CALCULATE(
		SUMX(
			SUMMARIZE( 
				'Date Table', 
				'Date Table'[Date], 
				'Date Table'[Month Num] 
			),
			CALCULATE(
				DISTINCTCOUNT('Random Data Generator'[Product ID] )
			)
		),
		REMOVEFILTERS( 'Date Table'[Date] ),
		VALUES( 'Date Table'[Date]),
		'Date Table'[Date] <= _MaxDay
	)

Simple Measure

YTD Sales = 
	CALCULATE(
    		[Total Sales],
    		DATESYTD( 'Date Table'[Date] )
	)

Testing method

I used DAX Studio to run my performance tests and only recorded the total query time. Each data point in the charts above represent an average of three trials. For example, For a dataset of 20K rows, I tested a complex measure three times. I repeated this process with simple measures for each model size, then repeated this process for visual calculations.

The Data

The fact table consisted of random numbers, product IDs, and dates like the following:

Index	Date	Net Amount	Product ID
1	Jan 1 2024	1092	PRD123
2	Jan 2 2024	509	PRD124
3	Jan 3 2024	1238	PRD125

Outlier Situations

I did come across one situation in which a visual calculation was much worse (slower) than its corresponding measure. And it stumped me for the longest time as for the reason why....

It turns out that using a power bi hierarchy on your x-axis will put a road bump in the performance of a visual calculation. Keep in mind that we still get performance benefits the larger the data model is. This is because visual calculations scale much better with the volume of data. So even in the image above visual calculations are slower, if the model itself was much bigger we would eventually see the visual calculation outperform the measure.

And by "hierarchies" I mean the following date hierarchy:

This is a default feature of Power BI described in this Microsoft article so it's good to keep in mind the performance costs if you are planning on using Visual Calculations.