Can I optimize calling same column in different measures?

Question

Hello,&nbsp;I have a Matrix that is displaying multiple versions of the same column.- Stock- Stock vs LY- Stock vs LY % = DIVIDE(Stock, Stock vs LY) -1The problem is that displaying Stock is not this fast so calling it three times is too long.Is it possible to optimize this by creating a measure to retrieve Stock and Stock vs LY to calculate Stock vs LY % (without a server request)?Or any other way?&nbsp;Thanks

greg_deckler · Answer

Anonymous&nbsp;- Is this a Direct Query situation? If so, you can speed things up and avoid round trips to your database by using Aggregation Tables.&nbsp;
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations

amitchandak · Answer

Anonymous , so this is basically complete data and year fewer data. Try this year vs last year that will reduce the data .
Also, I am assuming it is a measure like (not measure)
Stock vs LY =CALCULATE( sum('Stock'[Stock]), SAMEPERIODLASTYEAR( 'Date'[Date] ) )
&nbsp;
Examples -measure
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
&nbsp;
Do you have bi-directional joins. Try to reduce them
refer if these can help
&nbsp;
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-should-I-do/td-p/712567https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practiceshttps://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Relationships/td-p/712566
&nbsp;

amitchandak · Answer

Anonymous , can you share the formula of
&nbsp;
- Stock
- Stock vs LY

anonymous · Answer

Stock is a column from my database so I do not think it has a formula&nbsp;Stock vs LY =
CALCULATE( 'Stock'[Stock], SAMEPERIODLASTYEAR( 'Date'[Date] ) )&nbsp;&nbsp;

anonymous · Answer

Thank you, I will look at it. Yes I am in DirectQuery mode.&nbsp;Do you think Calculation groups can (significantly?) improve performances?

Forum Discussion

Can I optimize calling same column in different measures?

5 Replies

Related content

Optimize Measures

Optimize big measure

DAX Measure Optimization - Please help

Fabric Cost optimization

DAX performance optimization