Optimize Code Help

[airwolf39]

Hi all,

I have a ton of code that works, but is slow. I read that Vars and Switches can speed up things but when i try to use them it doesnt work. Here is my current measure (gives the correct answer):

if(HASONEFILTER('Single BOM Table'[Child Part]),

     [Child Parts Req] - [Makat_Inv_FR],

sumx(SUMMARIZE('Single BOM Table','Single BOM Table'[Child Part],"child",

    [Child Parts Req] - [Makat_Inv_FR]),

[child]*1))

When i try to convert it using Vars:

Makat Still Needs =

var child_parts_req = [Child Parts Req]

var makat_Inv_FR_ = [Makat_Inv_FR]

var result=

if(HASONEFILTER('Single BOM Table'[Child Part]),

child_parts_req - makat_Inv_FR_,

sumx(SUMMARIZE('Single BOM Table','Single BOM Table'[Child Part],"child",

child_parts_req-makat_Inv_FR_),

[child]*1))

return

result

The answer is different. What I am doing wrong?

[parry2k]

@airwolf39 all these measures need attention. I can see why it is super slow.

[parry2k]

@airwolf39 

1 to many is fine, any cross filter direction to both?

can you check with the performance analyzer what is going on? Which measure is taking longer. start with one measure and check performance and then add another one to see which one is causing the issue.

[airwolf39]

The problem is "FG Build". Which is built on top of a ton of measures. What im hoping for are some tips on how to make the measures faster.....

The measure that is causing the problem is this one:

FG Build =

if(ISBLANK([Max Makat Still Needs]),

blank(),

[FG Still Needs]-[Fill Rate Impact])

 --------------------------------------------------------------------

FG Still Needs =

var calc = CALCULATE([Open Sales Qty]-[Inv_OpenSO_FGFR],filter(parts,[Open Sales Qty]>0))

return

if(calc>0,calc,blank())

Fill Rate Impact =

var FG_S_N = [FG Still Needs]

var Max_S_N = 'Single BOM Table'[Max Makat Still Needs]

return

if(HASONEFILTER(Parts[BOM #]),

if(ISBLANK([Coef]),

FG_S_N, Max_S_N),

Max_S_N+ CALCULATE([FG Still Needs],filter(Parts,ISBLANK([Coef]))))

Max Makat Still Needs =

if(HASONEFILTER('Single BOM Table'[Child Part]) ||

(HASONEFILTER(Parts[BOM #]) ||

(HASONEFILTER(Parts[SPEC 5]))),

'Single BOM Table'[Max FR impact],

sumx(SUMMARIZE(Parts,Parts[SPEC 5],"spec_5",'Single BOM Table'[Max FR impact]),[spec_5]*1))

Max FR impact =

maxx(SUMMARIZE('Single BOM Table','Single BOM Table'[Child Part],"Child",

([Child Parts Req]-'Makat Inventory'[Makat_Inv_FR])),

[Child]*1)

Coef =

if(isblank([Open Sales Qty_Child]),blank(),

if(HASONEFILTER('Single BOM Table'[Child Part]),

CALCULATE(MAXX('Single BOM Table','Single BOM Table'[coeff]),

filter('Single BOM Table',[Open Sales Qty_Child]>0),filter('Single BOM Table','Single BOM Table'[coeff]>0)),

sumx(SUMMARIZE('Single BOM Table','Single BOM Table'[Child Part],"Child",

CALCULATE(SUMX('Single BOM Table','Single BOM Table'[coeff]),

filter('Single BOM Table',[Open Sales Qty_Child]>0),filter('Single BOM Table','Single BOM Table'[coeff]>0))),

[Child]*1)))

[parry2k]

@airwolf39 because in sumx you are using variables, the value is not getting calculated for each child part and that's why

There could be many reasons for the slow performance, how your data is modeled, and the relationship between tables.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[airwolf39]

All my tables have a 1 to many reletionship. Its very clean and utilizes unique keys.

I know that you have to keep variables inside of a calculate function if you want them to work properly. I didnt realize this also applies to sumx??