topic Re: DAX help needed - inherited code timing out in DAX Commands and Tips

DAX help needed - inherited code timing out

duncfair — Mon, 06 Dec 2021 16:36:36 GMT

Hi All,

Thanks in advance for any help you can send my way.

I have inherited a report with some (to me, anyway) complex DAX that I have been told is causing my time-out issues.

Please let me know what else I would need to post to provide enough information for assistance.

Thanks!

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

SelectedFcst =
VAR Mth = [SelectedKPI_Month]
VAR Qtr = [SelectedKPI_Qtr]
VAR CY = [Current_FY]
VAR CM = [Current_Mth]
RETURN
SWITCH([SelectedKPI_Version],
// Current Forecast
2, SWITCH([SelectedKPI_Time],
// FY
1, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Current]), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[Is_Current_Yr] = "Yes")),
// YTD
2, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Current]), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[Is_Current_Yr] = "Yes")),
// QTD
3, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Current]), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalQuarter] = Qtr), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalYear] = [Current_FY])),
// MTD
4, IF(Mth = CM, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Current]), vw_FiscalCal[SelectedMonth] = "Yes", vw_FiscalCal[Is_Current_Yr] = "Yes"), CALCULATE(SUM(Rev_Consol_Pivot_PowerBI[Current]), Rev_Consol_Pivot_PowerBI[FiscalMonth] = Mth, Rev_Consol_Pivot_PowerBI[FiscalYear] = CY)
)),
// Prior Forecast
4, SWITCH([SelectedKPI_Time],
// FY
1, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Prior]), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[Is_Current_Yr] = "Yes")),
// YTD
2, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Prior]), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[Is_Current_Yr] = "Yes")),
// QTD
3, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Prior]), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalQuarter] = Qtr), FILTER(ALL(vw_FiscalCal), vw_FiscalCal[FiscalYear] = [Current_FY])),
// MTD
4, IF(Mth = CM, CALCULATE(sum(Rev_Consol_Pivot_PowerBI[Prior]), vw_FiscalCal[SelectedMonth] = "Yes", vw_FiscalCal[Is_Current_Yr] = "Yes"), CALCULATE(SUM(Rev_Consol_Pivot_PowerBI[Prior]), Rev_Consol_Pivot_PowerBI[FiscalMonth] = Mth, Rev_Consol_Pivot_PowerBI[FiscalYear] = CY)
))
)
----------------------------------------------------------------------------------------------------------------------

Re: DAX help needed - inherited code timing out

bcdobbs — Mon, 06 Dec 2021 20:50:55 GMT

I haven't changed much, and I think what I have is mostly syntax sugar but hopefully it makes things a little more readable. Did however remove a second reference to [Current_FY] and used the pre calculated CY variable instead.

Can you see if this is any better (suspect not) and post any error messages you get.

SelectedFcst = VAR Mth = [SelectedKPI_Month] VAR Qtr = [SelectedKPI_Qtr] VAR CY = [Current_FY] VAR CM = [Current_Mth] RETURN SWITCH([SelectedKPI_Version], // Current Forecast 2, SWITCH([SelectedKPI_Time], // FY 1, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Current]), REMOVEFILTERS(vw_FiscalCal), vw_FiscalCal[Is_Current_Yr] = "Yes"), // YTD 2, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Current]), REMOVEFILTERS(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth, vw_FiscalCal[Is_Current_Yr] = "Yes"), // QTD 3, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Current]), REMOVEFILTERS(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth, vw_FiscalCal[FiscalQuarter] = Qtr, vw_FiscalCal[FiscalYear] = CY), // MTD 4, IF( Mth = CM, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Current]), vw_FiscalCal[SelectedMonth] = "Yes", vw_FiscalCal[Is_Current_Yr] = "Yes"), CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Current]), Rev_Consol_Pivot_PowerBI[FiscalMonth] = Mth, Rev_Consol_Pivot_PowerBI[FiscalYear] = CY) ), // Prior Forecast 4, SWITCH([SelectedKPI_Time], // FY 1, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Prior]), REMOVEFILTERS(vw_FiscalCal), vw_FiscalCal[Is_Current_Yr] = "Yes"), // YTD 2, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Prior]), REMOVEFILTERS(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth, vw_FiscalCal[Is_Current_Yr] = "Yes"), // QTD 3, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Prior]), REMOVEFILTERS(vw_FiscalCal), vw_FiscalCal[FiscalMonthofYear] <= Mth, vw_FiscalCal[FiscalQuarter] = Qtr, vw_FiscalCal[FiscalYear] = CY), // MTD 4, IF( Mth = CM, CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Prior]), vw_FiscalCal[SelectedMonth] = "Yes", vw_FiscalCal[Is_Current_Yr] = "Yes"), CALCULATE( SUM(Rev_Consol_Pivot_PowerBI[Prior]), Rev_Consol_Pivot_PowerBI[FiscalMonth] = Mth, Rev_Consol_Pivot_PowerBI[FiscalYear] = CY) )))

Re: DAX help needed - inherited code timing out

AlexisOlson — Mon, 06 Dec 2021 20:47:04 GMT

I'd expect this to help at least some having column filters rather than table filters.

P.S. There's one remaining [Current_FY] you probably want to replace.

Re: DAX help needed - inherited code timing out

bcdobbs — Mon, 06 Dec 2021 20:51:11 GMT

Good spot, have updated.

Re: DAX help needed - inherited code timing out

duncfair — Mon, 06 Dec 2021 20:55:06 GMT

Thanks!

error generated:

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Re: DAX help needed - inherited code timing out

duncfair — Mon, 06 Dec 2021 20:57:45 GMT

That must have been prior to the update. No error now. I will test and circle back.

Thank you so much!

Re: DAX help needed - inherited code timing out

duncfair — Mon, 06 Dec 2021 22:11:46 GMT

Unfortunately - still timing out.

Re: DAX help needed - inherited code timing out

bcdobbs — Mon, 06 Dec 2021 22:25:00 GMT

Can you tell us a bit about your infrastructure?

Specifically are you running in DirectQuery or Import mode?

Re: DAX help needed - inherited code timing out

duncfair — Mon, 06 Dec 2021 22:34:33 GMT

Import.

Re: DAX help needed - inherited code timing out

bcdobbs — Mon, 06 Dec 2021 22:50:21 GMT

List of thoughts...

If you drop the measure into a very simple card visual does it process?

In PowerQuery if you put a filter on to reduce data volume (eg limit it to previous 2 years) does the visual render? If so can you send a screen shot of what the visual is.

Other things to look at:

Remove large chunks of the switch statement (or comment out the chunks and have it return a static value). To try and narrow down a particular aspect of the query. (Nothing jumps out).

I'd normally paste the query for the visual into dax studio and have a look at it's execution in there.

Lastly I wondered if you could split the measure out into a calculation group and use that to dynamically change the measure rather than a switch statement. I think doing it that way would remove the full execution that I assume is currently happening.

Re: DAX help needed - inherited code timing out

bcdobbs — Mon, 06 Dec 2021 23:03:44 GMT

Another route to try is to pre aggregate the data at refresh time.

I can write some more specific code when next by a computer but you want a calculated table that summarises the table that gets summed grouped by all your date columns.

Re: DAX help needed - inherited code timing out

duncfair — Tue, 07 Dec 2021 14:50:03 GMT

The visual always renders in PowerBI desktop and occasionally renders in the service as well.

May have to try the pre-aggregation route.

Re: DAX help needed - inherited code timing out

duncfair — Mon, 13 Dec 2021 13:42:29 GMT

No change - same issue.