Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

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

@Anonymous , can you share the formula of

 

- Stock

- Stock vs LY

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

 

 

@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



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

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

@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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors