Forum Discussion
duncfair
Helper IV
4 years agoDAX 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...
bcdobbs
Community Champion
4 years agoI 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 agoI'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.
- bcdobbs4 years ago
Community Champion
Good spot, have updated.