Forum Discussion
Caching computed measures on row level
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!
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
1 Reply
- v-jiascu-msftMicrosoft Employee
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