Reuse calculated table variable within DAX formula

Question

I have the following DAX formula for a measure, and it works fine.

Gross Area = 
var as_on = LASTDATE('Calendar'[Date]) 
var all_latest_dates =
    ADDCOLUMNS(
        VALUES('Unit Revision'[Unit Id]),
        "Last Event", CALCULATE(
                            MAX('Unit Revision'[Event Date]),
                            'Unit Revision'[Event Date] <= as_on
                        )
    )
var unit_latest_dates = TREATAS(all_latest_dates, 'Unit Revision'[Unit Id], 'Unit Revision'[Event Date])
RETURN
CALCULATE( SUM('Unit Revision'[Gross Area]), unit_latest_dates )

I need to calculate more metrics using a similar logic, where the DAX formula is the same until the RETURN statement, and just the column name within the final CALCULATE( SUM() ) differs.

I then need to add all these measures to a Matrix. In such as case, will the variable calculation of the unit_latest_dates table be calculated for every DAX measure, or will it be cached? To ensure it is cached, is it possible to reuse the initial set of DAX code within a calculated table measure or something, just like how we can reuse a scalar measure in another measure?

I can't use this in a Calculated Table, as the calculation changes based on changes in the Calendar Table Date slicer.

If you have a basic version of the measure that makes a calculate(sum()), then you can use Calculation Group to not reproducing code. Powerbi is a SSAS Tabular version, here we have StoreEngine and FormulaEngine query -> storeEngine query is cached. This you can check inside DaxStudio ServerTiming feature. sqlbi.com/tv/… — msta42a

AntrikshSharma AntrikshSharma · Accepted Answer · 2021-06-21T17:25:17

Yes, it should be cached as long as DAX Engine can understand that a similar query was executed earlier and it didn't involve a CALLBACKDATAID and was executed only by Storage Engine and not Formula Engine, try to use DAX Studio and see if it is doing that or not.

Reuse calculated table variable within DAX formula

1 Answers