Can I optimize calling same column in different measures?

[Anonymous]

Hello,

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) -1

The 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?

Thanks

[amitchandak]

@Anonymous , can you share the formula of

- Stock

- Stock vs LY

[Anonymous]

Stock is a column from my database so I do not think it has a formula

Stock vs LY =
CALCULATE( 'Stock'[Stock], SAMEPERIODLASTYEAR( 'Date'[Date] ) )

[Greg_Deckler]

@Anonymous - Is this a Direct Query situation? If so, you can speed things up and avoid round trips to your database by using Aggregation Tables. 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations

[Anonymous]

Thank you, I will look at it. Yes I am in DirectQuery mode.

Do you think Calculation groups can (significantly?) improve performances?

[amitchandak]

@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] ) )

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))

Do you have bi-directional joins. Try to reduce them

refer if these can help

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/712567
https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Relationships/td-p/712566