Forum Discussion

duncfair's avatar
duncfair
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
    bcdobbs
    Icon for Community Champion rankCommunity 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's avatar
      AlexisOlson
      Icon for Super User rankSuper 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.

    • duncfair's avatar
      duncfair
      Icon for Helper IV rankHelper 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's avatar
        duncfair
        Icon for Helper IV rankHelper 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!

    • bcdobbs's avatar
      bcdobbs
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Can you tell us a bit about your infrastructure?

       

      Specifically are you running in DirectQuery or Import mode?

    • bcdobbs's avatar
      bcdobbs
      Icon for Community Champion rankCommunity 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's avatar
        bcdobbs
        Icon for Community Champion rankCommunity 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.