Forum Discussion
Using a Measure in a Calculated Column
Anonymous wrote:But I still didn't understand from you answer what is the calculation order DURING the refresh.
Lets' say I have a measure and I use it in a calculated column.
What happens first? The raw table is loaded - then the measures are calculated (not "by user's demand" but for calculated columns) - then the calculated columns are calculated and loaded to memory & refresh finishes?
There could be a chain dependency: a measure is used in a column that is used in another measure which is used in another column...
Yes, as you say. The logical order is determined by dependencies. The data is loaded first, then the columns are calculated before the refresh is technically finished. It can't finish refresh until the calc columns are created. I can't say for sure *which* calc column is done first (in the case there are more than 1), but they are all refreshed of course. If one depends on another (which you should avoid if practicable) then the column needed as input to another column is refreshed first. If a column needs a measure as an input, then that is calculated on demand. If the measure calls another calculated column, then that calculated column simply must be refreshed first. In a way it is the same as Excel. This is one reason a report with Calculated Columns will take longer to refresh than one without. Suffice to say, the logic is such that it works - sorry I can't be more technical than that beucase I don't know any deeper.
Anonymous wrote:
But I still didn't understand from you answer what is the calculation order DURING the refresh.
Lets' say I have a measure and I use it in a calculated column.
What happens first? The raw table is loaded - then the measures are calculated (not "by user's demand" but for calculated columns) - then the calculated columns are calculated and loaded to memory & refresh finishes?
There could be a chain dependency: a measure is used in a column that is used in another measure which is used in another column...
Yes, as you say. The logical order is determined by dependencies. The data is loaded first, then the columns are calculated before the refresh is technically finished. It can't finish refresh until the calc columns are created. I can't say for sure *which* calc column is done first (in the case there are more than 1), but they are all refreshed of course. If one depends on another (which you should avoid if practicable) then the column needed as input to another column is refreshed first. If a column needs a measure as an input, then that is calculated on demand. If the measure calls another calculated column, then that calculated column simply must be refreshed first. In a way it is the same as Excel. This is one reason a report with Calculated Columns will take longer to refresh than one without. Suffice to say, the logic is such that it works - sorry I can't be more technical than that beucase I don't know any deeper.