DAX help needed - inherited code timing out

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

12 Replies

bcdobbs

Community Champion

4 years ago

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

AlexisOlson
Super User
4 years ago
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
  Community Champion
  4 years ago
  Good spot, have updated.
duncfair
Helper IV
4 years ago
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
  Helper IV
  4 years ago
  That must have been prior to the update. No error now. I will test and circle back.
  
  Thank you so much!

duncfair
Helper IV
4 years ago
Unfortunately - still timing out.
- bcdobbs
  Community Champion
  4 years ago
  Can you tell us a bit about your infrastructure?
  
  Specifically are you running in DirectQuery or Import mode?
duncfair
Helper IV
4 years ago
Import.
- bcdobbs
  Community Champion
  4 years ago
  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.
  - bcdobbs
    Community Champion
    4 years ago
    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
Helper IV
4 years ago
No change - same issue.

Forum Discussion

DAX help needed - inherited code timing out

12 Replies

Related content

Inheritance of Values

need help

Time vs Time Scatter Chart

Need to have real time refresh from Bc to Power Bi

Need help with a date \ time field