Caching computed measures on row level

[Anonymous]

Hello,

I have a question regarding the optimization of a measure.
Assuming the following situation:

Connection to a SSAS cube.

One Measure "Delta" is computed for each row in a table. Now I want to compute the count of the rows, when "Delta" is between two values, i.e. -1,5 < "Delta" < 1,5.

I also have to do this for different intervals: -3 to -1,5 etc.

Since the computation of "Delta" is rather expensive, I would like to avoid to compute the same value multiple times.

Is there some way of chaching the result of "Delta"?

Small example:

Right now I am computing the count this way (simplified for readability):

+/- 1,5= 
VAR minDateP = ...
VAR maxDateP = ...
    
VAR minDateM = ...
VAR maxDateM = ...

RETURN
    SUMX (
        FILTER('Company';'Company'[Id] IN VALUES('Company2'[Id]));
        VAR pP = CALCULATE(SUM('Sales'[Amount]);FILTER('Calendar';'Calendar'[Date]>=minDateP && 'Calendar'[Date]<=maxDateP))
        VAR pM = CALCULATE(SUM('Sales'[Amount]);FILTER('Calendar';'Calendar'[Date]>=minDateM && 'Calendar'[Date]<=maxDateM))

        
        VAR delta = pP -pM
        RETURN
            IF ( delta > -1,5 && delta < 1,5 && NOT ( ISBLANK ( delta ) ); 1; 0 )        
    )
    

Thanks for any help!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Since the calculation of measures needs context, I'm afraid "storing the results of a measure" is also expensive. There could be workarounds for each specialized scenario. That is the calculated table. The result of measure will be stored in this scenario.

Best Regards,

Dale