Forum Discussion

AMNM2023's avatar
AMNM2023
Regular Visitor
3 years ago

Optimizing Dax measure that add

Hello, I am looking for help with some DAX measures that I created.  They do work, I just need help with optimizing them so they can run a bit better.   The data set that I am using is comprised of...
Need Help
Tips and Tricks
DarkArchonNL's avatar
DarkArchonNL
New Member
3 years ago

  1. Pre-Calculated Columns: Some variables in the measure are calculated based on static conditions and they can be moved to the table as calculated columns. For instance, BPActive can be pre-calculated at the row level in the 'Fact Table'.

  2. Use of ALL function instead of REMOVEFILTERS: In some instances, you can use the ALL function instead of REMOVEFILTERS, which tends to be more efficient. You can replace CALCULATE (MIN ( 'Date Table'[End of Month] ), REMOVEFILTERS ()) with MINX(ALL('Date Table'), 'Date Table'[End of Month]).

  3. Filtering Optimization: Instead of using the KEEPFILTERS function, try filtering within CALCULATE directly. For example, replace CALCULATE (SUM ( 'Fact Table'[RawCost] ), 'Budget List'[Budget Order] = 19, KEEPFILTERS('Fact Table'[PeriodNbr Integer] = 1), REMOVEFILTERS ( 'Budget List'[Forecast Label] )) with CALCULATE (SUM ( 'Fact Table'[RawCost] ), 'Budget List'[Budget Order] = 19, 'Fact Table'[PeriodNbr Integer] = 1, ALL( 'Budget List'[Forecast Label] )).

  4. Simplify the Switch Statement: The switch statement has repeated conditions which can be grouped together to avoid redundancy, if it doesn't alter the logic of your calculations.

Remember, when optimizing, testing the performance after each change is crucial. The DAX Studio tool can help you in evaluating the performance of the DAX expressions, I highly recommend it!