cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Srikantht
Helper I
Helper I

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 8
mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


bcdobbs
Super User
Super User

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi ,

 

here is the Columns,

 

Srikantht_0-1660361469135.png

 

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?

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi ,

 

1. There are 3 tables (Calendar table, Merch_Forecast_snapshot table, Article table) and also have relationship. 

2. we brought the columns cal date month, Le month index from Calendar table by using related dax function to the Merch forecast_snapshot table. 

3. I shared table details the above screen shot those columns only availble in Merch_Forecast_snapshot table.

4. MERCH_FORECAST_SNAPSHOT[_Raw Forecast Qty] is a measure  (sum of forecast qty)

 

5. From article table we are used Major code, Minor code & family code categories. 

6. I am showing the data in Matrix table  - rows are Le fiscal period, Major code, Minor code & family code. values are 1 mo th, 2 month , 3 month,etc.,(up to 6 months) measures.

7. I think as per the previous conversion i attached dax studio performnace screen shot there you can see 6191 rows which are from  Major code, Minor code & family code categories. as my understanding performnce is slow because of 3 columns (6191 rows).

if i remove those 3 rows under matrix visulal only if keep Le fiscal period its getting below 1000 ms. 

Srikantht_0-1660620705977.png

 

 

I think I explain detailed and I don't want to remove those 3 columns from rows in visual . i want to show the data with 4 rows (Le fiscal period, Major code, Minor code & family code.) and is there any way to write/add a dax to that particular column to filter it quickly. 

 

help me from this.

 

Thanks. 

 

 

 

Srikantht
Helper I
Helper I

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)

 

Srikantht_0-1660322939850.png

 

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.

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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..... 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.