Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Pha
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
*/
 
Can someone please help me optimize these codes to get better DAX query performance
5 REPLIES 5
Pha
Frequent Visitor

Can anyone please help with simplifying the above DAX codes

Read about EDATE()

lbendlin
Super User
Super User

Are you familiar with DAX Studio?

Pha
Frequent Visitor

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.