DAX help needed - inherited code timing out

[duncfair]

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

[duncfair]

No change - same issue.

[duncfair]

Import.

[bcdobbs]

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.

[duncfair]

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

May have to try the pre-aggregation route. 

[bcdobbs]

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.

[duncfair]

Unfortunately - still timing out.

[bcdobbs]

Can you tell us a bit about your infrastructure?

Specifically are you running in DirectQuery or Import mode?

[bcdobbs]

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

[duncfair]

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.

[duncfair]

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

Thank you so much!

[AlexisOlson]

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.

[bcdobbs]

Good spot, have updated.