cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

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

8 REPLIES 8
Employee

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!

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

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

Hi ,

here is the Columns,

Super User

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:

Lastly is MERCH_FORECAST_SNAPSHOT[_Raw Forecast Qty] a measure? If so can you share it's code?

Ben Dobbs

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

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.

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.

Helper II

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.

Super User

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

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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors