cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Visual has exceeded available resources. How to improve DAX query performance?

Hi All,

I have a Matrix visual that is used to calculate various measures which when published to service gives an error "Visual has exceeded available resources"

I have narrowed down to 3 measures whose performance is causing the matrix to slow down.

Last Period of Favourable Var (In 1 Year) =
VAR __YearShift = 1 //max number of years to look back
VAR __MaxDate = MAX('Calendar'[Date]) //latest date
VAR __MinDate = DATE(YEAR(__MaxDate) - __YearShift, MONTH(__MaxDate), 1) //the first date to appear in calendar
VAR __FilteredCal = FILTER(ALL('Calendar'), 'Calendar'[Date] >= __MinDate && 'Calendar'[Date] <= __MaxDate) //calendar filtered by min and max dates defined above
VAR __LastPeriod = CALCULATE(LASTNONBLANK('Calendar'[Date], [Positive Var]), __FilteredCal) //Calculates last period
RETURN IF(ISBLANK(__LastPeriod), __MinDate, __LastPeriod) //returns min period if there is blank because we only look back that far

# of Months Unfavourable Var =
Var x=[Last Period of Favorable Var (In 1 year)]
Var y=[MaxDate]
return(DATEDIFF(x,y,MONTH))

Sum in period =
CALCULATE([Gross Margin Var],DATESBETWEEN('Calendar'[Date],[Next Month Same Day],MAX('Calendar'[Date])))

/*
Next Month Same Day =
VAR CurrentDate = [Last Period of Favorable Var (In 1 year)]
VAR FirstDayOfNextMonth = EOMONTH(CurrentDate,0) + 1
VAR DaysToSameDay = DAY(CurrentDate) - 1
RETURN FirstDayOfNextMonth + DaysToSameDay
*/

5 REPLIES 5
Frequent Visitor

Super User

Super User

Are you familiar with DAX Studio?

Frequent Visitor

@lbendlin I haven't used it but I can try. Please guide

Super User