Forum Discussion
How to Optimize my Complex dax query
Hi Guys,
I really appriciate if some one gives solution for the below dax query..
The below dax query need to be optimized. How? this query running 2400 ms. So I want to optimize it.
1 Month Forecast Absolute Accuracy =
VAR LE = MAX ( MERCH_FORECAST_SNAPSHOT[LE Month Index] )
VAR MaxLE = LE
VAR MaxActul = CALCULATE(MAX ( MERCH_FORECAST_SNAPSHOT[Caldate Month Index] ),MERCH_FORECAST_SNAPSHOT[Actuals Flag]="1" ,
ALL(MERCH_FORECAST_SNAPSHOT[Forecast Scenario]))
VAR ActualQty =IF(MaxActul<MaxLE,0,
CALCULATE (
MERCH_FORECAST_SNAPSHOT[_Raw Actual Qty],
ALL ( MERCH_FORECAST_SNAPSHOT[LE Fiscal Period] ),
MERCH_FORECAST_SNAPSHOT[Caldate Month Index] >= LE
&& MERCH_FORECAST_SNAPSHOT[Caldate Month Index] <= MaxLE
))
VAR ForecastQty = CALCULATE (
MERCH_FORECAST_SNAPSHOT[_Raw Forecast Qty],
MERCH_FORECAST_SNAPSHOT[Caldate Month Index] >= LE
&& MERCH_FORECAST_SNAPSHOT[Caldate Month Index] <= MaxLE)
VAR _AbsoluteDiff = SUMX(VALUES(ARTICLE[Article]),Calculate(ABS(ForecastQty-ActualQty)
))
VAR
_Accuracy = IF(ActualQty,ABS(
1- DIVIDE(_AbsoluteDiff,ActualQty,0)))
Return
_Accuracy
Thank you in Advance.
8 Replies
Hi Guys,
Here is the Peformance of this Query in dax studio. and using matrix visual & there are 4 rows (LE fiscal period, Major code, Minor code & family code)
Help me where the performace is slow ?
Thanks.
A few questions to start with...
You have:
VAR MaxLE = LE
Then go onto use them as if they were different. Is that correct?
Later on you reference two variables inside a calculate on a sumx:
Calculate(ABS(ForecastQty-ActualQty)
Once assigned to a variable quanties don't get revaluated inside a filter context. Are you sure you're getting the correct answers? If you are you would get same value by removing the sumx and simply multiplying the absolute difference by the number of articles which is quicker to find with a COUNTROWS.
HI ,
Thank you for responding.
MaxLE = LE - using to add like (LE+1, LE+2 ) for 2 months, 3 months forecast accuracy. Now this is for 1 month.
sorry for the above query.
Here is the chaged query & the above performace screen shot is the below query.
_Raw 1 month Forecast Accuracy =
VAR LE =
MAX ( MERCH_FORECAST_SNAPSHOT[LE Month Index] )
VAR MaxLE = LE
VAR MaxActul = CALCULATE(MAX ( MERCH_FORECAST_SNAPSHOT[Caldate Month Index] ),MERCH_FORECAST_SNAPSHOT[Actuals Flag]="1"
,ALL(MERCH_FORECAST_SNAPSHOT[Forecast Scenario]))
VAR ActualQty =IF(MaxActul<MaxLE,0,
CALCULATE (
MERCH_FORECAST_SNAPSHOT[_Raw Actual Qty],
ALL ( MERCH_FORECAST_SNAPSHOT[LE Fiscal Period] ),
MERCH_FORECAST_SNAPSHOT[Caldate Month Index]>= LE
&& MERCH_FORECAST_SNAPSHOT[Caldate Month Index] <= MaxLE
))
VAR ForecastQty =
CALCULATE (
[_Raw Forecast Qty],
MERCH_FORECAST_SNAPSHOT[Caldate Month Index] >= LE
&& MERCH_FORECAST_SNAPSHOT[Caldate Month Index] <= MaxLE
)
Return
IF(ActualQty,
1- DIVIDE ( ABS ( ForecastQty - ActualQty ), ActualQty,0) )for this you can help me please.
Thanks.....
Can you send a picture of your data model? It looks like you're running a single table and so everything requires a full scan of the table. Can you send a list of all columns?
If we can separate off dimensions into a star schema I suspect we can improve efficiency.
Hi ,
here is the Columns,
Hi.
I think you need to thin the table down.
Turn off Power BI's auto datetime functionality.
Separate your date related columns into a date table. (Suspect you might need two... what are the LE columns?)
Lots of blogs on date tables but this might get you started:
https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/You also want to separate related flags out into a separate dimension:
https://m.youtube.com/watch?v=vZndrBBPiQc
https://m.youtube.com/watch?v=vjBprojOCzU
Lastly is MERCH_FORECAST_SNAPSHOT[_Raw Forecast Qty] a measure? If so can you share it's code?
Just looking at the end of your expression, I see two issues.
VAR _AbsoluteDiff = SUMX(VALUES(ARTICLE[Article]),Calculate(ABS(ForecastQty-ActualQty)
))// the SUMX is likely not doing what you expect. you are referencing variables ForecastQty and ActualQty. those are being evaluated once and then reused for however many distinct Articles you have (not calculated for each).
VAR
_Accuracy = IF(ActualQty,ABS(
1- DIVIDE(_AbsoluteDiff,ActualQty,0)))// ActualQty is a variable that returns a number. the first argument of the IF should be a true/false
Hard to tell w/o seeing your data but I suspect you could create a table variable with ADDCOLUMNS(SUMMARIZE(... where you add columns for forecast and actual qty, and then apply your logic in the SUMX over that table variable.
Pat
