Using a Measure in a Calculated Column

[Anonymous]

Hello friends,

I have a MEASURE that calculates the most recent date in my calendar table

LastDate = CALCULATE(MAX(Calendar[Date]),ALL(Calendar))

I want to use it in a CALCULATED COLUMN Calculation:

DaysAgo = LastDate - [Date]

It works, but I need to be sure HOW it is calculated in DAX:

As far as I understand, the calulated columns are calculated when the model is PROCESSED.

But when is my measure calculated? Before or after the Process?

And what if I have another measure that is based on my DaysAgo column and another column that is based on that measure.

I want to make sure how the calculation order works.

Will appreciate information / links to sources on the subject.

Thank you

Michael

[MattAllington]

Calc columns are only refreshed when the data is loaded - they don’t respond to Report interaction. That is one reason I recommend avoiding them (among many other reasons) https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

Measures are calculated on demand based on the report selections, not the refreshed the data. So your measure in the column is calculated on demand, in the case of the column it is at data refresh. 

Other measures that point to your column will refer to the static data in the column as calculated at the last refresh. When this measure is calculated depends on where it is used. If in a report, it will respond to the report settings and filters. 

People tend to write calc columns because they find it easier than writing measures (not the only reason, but a common reason). Can you write a measure instead?

[Anonymous]

@MattAllington 

Thanks, Matt

No, I cannot write measure instead, because I am doing some row-based calculations for use as attributes in matrix/other visuals that require columns.

I absolutely understand that measures are calculated on demand and that columns are calculated at the refresh.

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...

I am not worried about circular reference (I can handle that logically) but I need to know the calculation ORDER actually works.

Any links to this information would be appreciated

Thanks!

Michael

[MattAllington · Accepted solution]

---

@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]

@MattAllington 

Thanks Matt,

Yes, this makes perfect sense!

Thank you.

Michael

[v-jiascu-msft]

Hi Michael,

Based on my knowledge and experience, I would say we just use the formula of a measure when we use it in a calculated column. As we all know, measures will be evaluated on demand and respond to the context. So there couldn't be a measure in the middle steps. When the data is loaded, the calculated column will be evaluated. About the difference, please refer to calculated-columns-and-measures-in-dax.

Best Regards,

Dale