How would you optimize SUMX with nested Switch or IF? ...its taking around 25sec to run

[Joorge_C]

Hi guys, thanks for the help.

Need a meassure to look at the Quarter and do sum A+B for the past months and C for the current and future.

Any ideas how I can optimize the below?

SUMX('All Data',

                    SWITCH(TRUE() ,

                            All Data'[MonthNum]>= Todays_month ,    CALCULATE(SUM('All Data'[A]))+CALCULATE(SUM('All Data'[B])),

                           'All Data'[MonthNum]< Todays_month ,    CALCULATE(SUM('All Data'[C]))

                         )

)

[Joorge_C · Accepted solution]

Hi all,

so what I ended up doing is a combination of a few Calculate Sums, I did not use the Sumx as it continued to take over 10 secs.

I also added a day condition to the filtering criteria.

Thanks all for the ideas.

Here is a snippet of the meassure:

var _todaysMonth = MONTH(TODAY())

var _todaysDay = DAY(TODAY())

return

CALCULATE(SUM('All Data'[A]),MONTH('All Data'[Date])<=_todaysMonth && _todaysDay <=7) +CALCULATE(SUM('Net[B]),MONTH('Net'[DateForMonth])<=_todaysMonth && _todaysDay<=7) +

CALCULATE(SUM('All Data'[A]),MONTH('All Data'[Date])=_todaysMonth && _todaysDay>7) + CALCULATE(SUM('Net'[B]),MONTH('Net'[DateForMonth])=_todaysMonth && _todaysDay>7)+

CALCULATE(SUM('All Data'[A]),MONTH('All Data'[Date])>_todaysMonth) + CALCULATE(SUM('Net'[B]),MONTH('Net'[DateForMonth])>_todaysMonth)+

CALCULATE(SUM('All Data'[C]),MONTH('All Data'[Date])<_todaysMonth && _todaysDay>7)

[Joorge_C]

Thanks all for the suggestions and help. Im going to be trying out and see which one works best. Will update later.

Thanks again!

[v-alq-msft]

Hi, @Joorge_C 

I'd like to suggest you try the following measure to see if it helps.

Measure =
SUMX (
    'All Data',
    SWITCH (
        TRUE (),
        'All Data'[MonthNum] >= Todays_month, 'All Data'[A] + 'All Data'[B],
        'All Data'[MonthNum] < Todays_month, 'All Data'[C]
    )
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

I would do a couple bigger calculations instead of lots of little ones, as follows:

Assuming Todays_month is a variable in the same expression,

New measure =
VAR Todays_month =
    MONTH ( TODAY () ) //or whatever your current approach is
VAR __prevsales =
    SUMX ( FILTER ( 'All Data', 'All Data'[Month] < Todays_month ), 'All Data'[C] )
VAR __currentfuturesales =
    SUMX (
        FILTER ( 'All Data', 'All Data'[Month] >= Todays_month ),
        'All Data'[A] + 'All Data'[B]
    )
RETURN
    __prevsales + __currentfuturesales

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[parry2k]

@Joorge_C what is todays_month, you need to use some aggregation if you are adding this as a measure

SUMX('All Data',
                    SWITCH(TRUE() ,
                            MAX('All Data'[MonthNum])>= Todays_month ,    CALCULATE(SUM('All Data'[A]))+CALCULATE(SUM('All Data'[B])),
                           MAX('All Data'[MonthNum])< Todays_month ,    CALCULATE(SUM('All Data'[C]))
                         )
)

assuming Todays_Month is a measure.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

[Joorge_C]

Thanks for the quick reply

todays month its just a var to look up the current month number

var _todaysmonth = month(today())