Forum Discussion

ddbaker's avatar
ddbaker
Icon for Helper I rankHelper I
4 years ago
Solved

Measure Calculations are Really Slow

Hey everyone, I have several measures that I'm using that are really slowing my report down. Wondering if any of you can provide any tips to make them more efficient.       ARR (Monthly) = VAR ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    The simplest approach would be to modify your Subscriptions table and append it to your invoices table, so it looks something like this. I just added an Invoice Date to it with Date.From(DateTime.LocalNow()), and renamed the Amount column.

     

    Simpler model leads to much simpler DAX.

     

     

     

    Pat

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

HI ddbaker,

I'm not so sure why you add so many calculations in your expression. Dax measure formulas are calculated based on row context level, if you nested lots of calculations in the same formula, they will iterator and calculate on each row.

Row Context and Filter Context in DAX - SQLBI

Optimizing DAX expressions involving multiple measures - SQLBI

DAX Best Practices | MAQ Software Insights

For this scenario, I'd like to suggest you do these sub calculation on a variable table and use the iterator function to summary these variable tables that stored the calculation results.

For example:

Measure =
VAR first_Aggregate =
    SUMMARIZE (
        Table,
        Table[Category],
        "Result", 'running calculated formula based on current date period'
    )
RETURN
    SUMX ( first_Aggregate, Result )

Regards,

Xiaoxin Sheng