Is it possible to refer to the SAME temporary variable Table in TWO different measures?

[Regex]

Hi,

I have 2 measures measure_1 and measure_2. Both utilize the exact same temporary variable table TempTable to calculate the final result. The calculation of TempTable is very heavy and depends on the filter context. Thus the exact same calculation is done TWICE, and it would make sense to reuse TempTable instead.

Is it possible to reduce total calculation time by 50% by e.g.

1. In measure_2, can we somehow refer to VAR TempTable already calculated in measure_1 ?
2. Or, can we generate TempTable on-the-fly as a calculated table inside the Data Model, and then refer to it from both measure_1 and measure_2 ?
3. Any other ideas?

I'm using PowerPivot, but i'm also interested if it is possible in Power BI.

Thanks!

[AntrikshSharma]

Nope variables are local to the measure they are defined in are not available globally, if you could just post the code here we could still try to understand how to optimize it.

[Regex]

Thanks Antriksh, but I already spent several days optimizing the code with Dax Studio.

This is a huge weakness in DAX compared to an SQL stored procedure.

I have an idea for a partial workaround though in PowerPivot/Excel. 

Step 1: EVALUATE the DAX code of TempTable in a worksheet connection in Excel.

Step 2: Query TempTable into the datamodel

Step 3: Calculate measure_1 and measure2

Subsequent problems

• EVALUATE can't see the filter context of the data model?
• User has to trigger the query?

[AntrikshSharma]

EVALUATE is just a syntax to run the code and nothing else. What you define on rows and columns would be a part of SUMMARIZECOLUMNS by default anything else will have to be injected with TREATAS

[Regex]

SUMMARIZECOLUMNS doesn't see any filter context if it runs through the "Edit DAX" on an Excel Table connected to the Data Model.

Seems like there is no solution to this issue.

[AntrikshSharma]

I wouldn't say that, as you haven't provided any DAX or data at all.