Forum Discussion
Preload measures into model
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.
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.
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.
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.