Memoisation in DAX measures

[Pierrecl3]

Hello everyone,

I do have to face a memory error in my DAX computations.
To by-pass it, I have in mind to apply some memoisation. Do you think memoisation is possible in Power BI?

Here is the context:
- I have to work on some data related to 1000 videos;
- measure A computes an average of the data per video;
- measure B computes the average of measure A results but excludes the current video related to context filter of measure A computation.

The issue is that:
- measure B re-runs for each video - in context filter - measure A computation for all other videos.
Then the time complexity becomes exponential, which finally raises space complexity issuing the memory error.

So is it possible to specify - in the measures formulae - to compute measure A for all videos just once, cache all the results to make measure B computation lighter?

Thanks in advance for your insights!
Pierre

 

[tallan]

Have you considered using Power Query to create a table which effectively would consist of an average of the data per video?

Another option, if you are using DAX in Power BI, would be to materialize the results of DAX query for measure A against all videos (in Power BI Desktop, on the Modeling tab there is an option for New Table)? Another option, if you are using Excel 2016 or Power BI, would be to use a variable to store the results of a DAX query (variable holding table is released at the end of the calculation that declared and initialized the variable).

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI

 

[Pierrecl3]

Thanks Tom for your answer.

Have you considered using Power Query to create a table which effectively would consist of an average of the data per video?

Another option, if you are using DAX in Power BI, would be to materialize the results of DAX query for measure A against all videos (in Power BI Desktop, on the Modeling tab there is an option for New Table)? Another option, if you are using Excel 2016 or Power BI, would be to use a variable to store the results of a DAX query (variable holding table is released at the end of the calculation that declared and initialized the variable).

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI

However what I did not mention first that the initial data per video - on which measure A computes an average - needs to be reachable by the explicit filters specified in "Filters" PivotTable Field and whose values are selected in the filters area of the Excel spreadsheet.

With that additional requirement, I do not think that creating intermediary tables or variables is a flexible enough solution. What do you think?