cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Grob2
Helper II
Helper II

Can I optimize calling same column in different measures?

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

5 REPLIES 5
amitchandak
Super User
Super User

@Grob2 , can you share the formula of

 

- Stock

- Stock vs LY



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

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

 

 

@Grob2 - 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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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

@Grob2 , 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-sho...
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-Rel...

 



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors