Percentage change speed

DAX DAX Calculations
1

Hi,

I am trying to build a P&L based on the Finance Reporting showcase video but i am having real performance issues relating to a percentage change measure it’s taking forever to calculate.

My report has 4 slicers:
Year - options are 2018, 2019 or 2020
Currency - options are GBP or LCY
Entity - options are UK, Norway or USA
Comparison - options are Budget or Prior year

I’ve loaded my code into Dax studio which tells me I have 608 SE queries which watching some other videos anything above 20 is not the best.

Any help would be appreciated, this is my measure:

CY v LY % =
VAR CurrentItem = SELECTEDVALUE(‘P&L_Template’[Items (Normalized)])

Return
SWITCH(TRUE(),

CurrentItem = "Revenue", DIVIDE([CY v LY], [Revenue LY],0)*1000,
CurrentItem = "Cost of Sales", DIVIDE([CY v LY], [COS LY],0)*1000,
CurrentItem = "Overheads", DIVIDE([CY v LY], [Overheads LY],0)*1000,
CurrentItem = "EBITDA", DIVIDE([CY v LY], [EBITDA LY],0)*1000,
CurrentItem = "Profit Before Tax", DIVIDE([CY v LY], [PBT LY],0)*1000,
CurrentItem = "Tax", DIVIDE([CY v LY], [Tax LY],0)*1000,
CurrentItem = "Profit After Tax", DIVIDE([CY v LY], [PAT LY],0)*1000,
CurrentItem = "Gross Margin", DIVIDE([CY v LY], [Gross Margin LY],0)*1000,
CurrentItem = "Gross Margin %", [Gross Margin %] - [Gross Margin % LY],
CurrentItem = "EBITDA %", [EBITDA %] - [EBITDA % LY])

This is the actual query that i loaded into Dax studio:

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({“UK”}, ‘Entities’[Country])

VAR __DS0FilterTable2 =
TREATAS({“GBP”}, ‘Currency’[Currency Selection])

VAR __DS0FilterTable3 =
TREATAS({“2019”}, ‘Dates’[Year])

VAR __DS0FilterTable4 =
TREATAS({“Prior Year”}, ‘Comparison’[Comparison])

EVALUATE
TOPN(
501,
SUMMARIZECOLUMNS(
‘P&L_Template’[Row Index],
‘P&L_Template’[Income Statement Items],
__DS0FilterTable,
__DS0FilterTable2,
DS0FilterTable3,
DS0FilterTable4,
“Selected_Year_Actuals”, ‘Annual Analysis’[Selected Year Actuals],
“Comparison_Actuals”, ‘New Measures’[Comparison Actuals],
"Comparion_Diff", ‘Visual Selections’[Comparion Diff.],
"Comparion_Diff", ‘Visual Selections’[Comparion Diff. %]
),
‘P&L_Template’[Row Index],
1,
‘P&L_Template’[Income Statement Items],
1
)

ORDER BY
‘P&L_Template’[Row Index], ‘P&L_Template’[Income Statement Items]

2

Hi @BCS, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

4

Hello @BCS,

Thank you for posting your query onto the Forum.

I would just like to highlight some of the facts which are noteworthy while improving the performance of the reports. There’s not a particular standardized bar where it defines that a particular speed is best for the reports since it really depends on multiple factors such as -

  1. How a particular data model is designed.
  2. A Measure written is using how much Storage Engine and how much Formula Engine. In this case the DAX is using maximum part of the Storage Engine.
  3. How our measure is written because this will have to do with the iterations that it will be performing in order evaluate the desired answer.
  4. If we’re doing some simple mathematical operations such as divide, multiply, addition or subtraction the query can run much faster as compared to the logical functions such as IF, SWITCH, etc. due to the iterations involved.
  5. And lastly since the measure mentioned above are the branching measures we need to analyze and focus on each of their parent measures which causes the performance issue.

And since you’re analyzing the DAX performance in DAX Studio. I’ve mentioned the some of the links where you can find an helpful article and a video on it which is explained in depth by Alberto Ferrari which can help you to improve the performance of your reports.

Hoping this is helpful in achieving your desired analysis.

Please feel free to write back in case I’ve missed on anything.

Thanks & Regards,
Harsh

[DAX Optimization Video] -
(https://www.sqlbi.com/tv/dax-optimization-examples/)

[Article on DAX Best Practices] -
(https://maqsoftware.com/expertise/powerbi/dax-best-practices)

2 Likes
5

Hi @BCS, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!