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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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


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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors