Preload measures into model

[Anonymous]

Hello, I'm building a report with a huge amound of data, and then I'm presenting this data in a matrix with a lot of calculated measures which are also SWITCH()es between 3 other measures. Endresult is the matrix is loading for ages when going a level below.

Of course once I load a lower level I can go level higher and below on the fly as the measures are already calculated.

Is it possible to preload the measures in the model? So that Power BI would begin to calculated those measures as soon as possible.

Before I used SWITCH() function I used bookmarks to hide/unhide matrixes with different measures - then it did load faster, however I wanted to connect drillthrough pages with the bookmarks and that is not possible. So instead im using those dynamic SWITCH measures connected to slicer filter that is synced between pages, but those masssive measures just take an amazing amount of time to calculate.

[Anonymous · Accepted solution]

By definition, measures cannot be precalculated as they depend on the filter context. So the answer to your question is no.

What you can do is precalculate tables and columns: if you have a "fixed" context when your value is calculated, you can precalculate the measure and store it in a column.

For example, i have a Forecast measure that needs to be calculated on a range 1-10 as these are the days of the forecast. I have a filter on page where you can choose the number of days, and the measure will take into account. However, as this measure is calculated in a matrix and so with several different filter context (one for each row) I precalculate all 10 values of this measure in a table and use a column "days" to choose which day it to show.

It's not always doable but can help rethinking your model on "what" can be calculated in advance. The more, the better.

Also, investigate of bottlenecks of your model. Sometimes a small change in a DAX formula can lead to massive improvements.

[Anonymous]

Thanks, I will go through formulas and think of another way to do this, it's probably possible as it often is the casse.

In this instance I don't think columns instead of measures will be an option, however I always thought it's better to use measures if possible as opposed to columns for general model performance. I suppose a balance between those would be optimal.

[Anonymous]

Well, the major advantage of columns OR calculated tables are that they're calculated when the model load, and not when the filter context changes. So, as a general rule, the less you need to calculate (and you can simply select from a precalculated value) the faster the model is. 
However, if you have 10.000.000 rows, even a simple additional column will allocate additional memory, so...think about it.