Using variables (or something similar) in multiple expressions

DAX DAX Calculations
1

To optimize performance, we use variables to prevent the same calculation from occurring multiples times in one expression. I’m wondering if I can do something similar with multiple expressions… Performing a calculation once rather than performing the same calculation in every measure in a view.

For example, if I have a MAXX expression and I need to refer to the output of that expression in multiple measures in a table, is it possible to run it just once rather than run it in every measure?

2

@Eric If MAXX is causing an issue then maybe you need to look into the table that you are supplying to it?

3

what you are looking for is called Measure Branching, and this is a technique that Sam uses throughout his training videos.
Below are a few forum posts that should help:

https://forum.enterprisedna.co/t/measure-branching/5962?u=heather
https://forum.enterprisedna.co/t/this-is-where-the-measure-branching-methodology-really-shines/4944?u=heather

4

@Heather - Thank you - I am using measure branching but the issue is that the same calculation is still being performed each time that measure is referenced. The advantage with variables is that the calculation is performed just once, and that can be referenced multiple times in the expression. I’m wondering if that same optimization is possible outside of one expression.

5

Hi @AntrikshSharma - I wish I could I avoid MAXX altogether but I don’t think I can. The table that I am supplying MAXX to has just 3 columns, AccountNumber, Zip Code and Sales. Zip code is used to join the table with the store list look up table so that when I filter by an account in store list, the MAXX table will be filtered to only the zip codes where that account operates. However, I need the maximum sales by account as an aggregate of these zip codes, not the maximum zip code, so I use the following expression to calculate the maximum sales by any account, excluding the account in scope, in the zip codes where the account in scope has stores.

    var _SelectedAccount = if(HASONEVALUE(StoreList[Account_Number]),
firstnonblank(StoreList[Account_Number],true),0)

    var _MaxVol =
    maxx( addcolumns( 
    summarize( Market,Market[UniqueAccountNumber]),
    "Max Vol", calculate(sum(Market[Sales_Volume]),
    keepfilters(Market[UniqueAccountNumber]<>_SelectedAccount))),[Max Vol])

Please let me know if you have a suggestion for optimization

6

@Eric Difficult to say anything without looking at the PBI file, also I think you forgot to include Zip Code in your code? Also, is there any performance issue in the report or are you trying to be proactive in optimizing it? Try to run each measure in DAX studio and see the time taken by each query.

7

@AntrikshSharma Zip code isn’t in the expression because I only want the table to be filtered by the zip codes in the store list lookup table (there is a relationship on zip code). I don’t want the maximum sales to be by zip code, but by the aggregate of zip codes. So, that’s why I have the virtual table, to find the max sales by account. You could think of it as putting the table in a pivot table with accounts in the rows, sales in values, and zip code is in the filter

There are performance issues when there is a matrix with multiple columns. For every measure, Power BI is summing the sales by account and finding the maximum, then dividing the maximum sales into total sales of the filtered zip codes. If the maximum sales is above 25%, the measures aren’t shown for confidentially reasons, if its under 25%, the measures are then calculated, so that’s why I was wondering if it’s possible to test this logic just once for all measures, rather than testing this logic in every measure.

8

Hi @Eric, 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 preformatted text </>.
    image

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include the masked 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.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

9

Hi @Eric, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!