topic Re: Running totals are taking too long and the visual exceeds time limit in DAX Commands and Tips

Running totals are taking too long and the visual exceeds time limit

CarlSagan — Thu, 10 Aug 2023 16:09:14 GMT

I have a visual that uses five measures which compute running totals based on dates. I was wondering if anyone could give me tips on optimizing the performance my code, I'm basically doing the same thing for each measure. Choose a specific date as the last date to sum up all previous totals under.

Beginning Balance = VAR lastD = LASTDATE(Assets[process_period_date]) var lastM = MONTH(lastD) VAR prevM = PREVIOUSMONTH(lastD) var begBal = CALCULATE( SUM(Assets[amount]), Assets[process_period_date] <= prevM ) RETURN begBal

Then I there is the final measure that sums up the previous five measure, I'm not sure if it is computing all of the measures again?

Last Ending Balance = [Beginning Balance] + [Last Earnings] + [Last Expenses] + [Last Distribution] + [Last Contributions] + [Last Transfer Total]

Am I supposed to be reusing variables so I don't need to compute them again?

Re: Running totals are taking too long and the visual exceeds time limit

AnthonyGenovese — Thu, 10 Aug 2023 17:59:23 GMT

What are the other measure definitions? I have an optimization idea but would need to see the other ones.

Another thing you can do is run each measure by itself, record times, and see which one is taking the longest. Then work on optimizing that one particular measure.

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Re: Running totals are taking too long and the visual exceeds time limit

CarlSagan — Thu, 10 Aug 2023 22:46:08 GMT

They are all pretty much the same

Last Contributions = VAR mostRecentMonth = LASTDATE(Assets[process_period_date]) VAR prevMonth = PREVIOUSMONTH(mostRecentMonth) var result = CALCULATE( SUM(Assets[amount]), Assets[tran_code] = 101 || Assets[tran_code] = 102 || Assets[tran_code] = 103 || Assets[tran_code] = 109 || Assets[tran_code] = 133, Assets[process_period_date] = mostRecentMonth ) RETURN result

Last Distribution = VAR mostRecentMonth = LASTDATE(Assets[process_period_date]) VAR distributions = CALCULATE( SUM(Assets[amount]), Assets[tran_code] = 501 || Assets[tran_code] = 502 || Assets[tran_code] = 503 || Assets[tran_code] = 504 || Assets[tran_code] = 509 ) RETURN distributions

Last Earnings = VAR mostRecentMonth = LASTDATE(Assets[process_period_date]) VAR earnings = CALCULATE( SUM(Assets[amount]), Assets[tran_code] = 301 || Assets[tran_code] = 302 || Assets[tran_code] = 305 || Assets[tran_code] = 309 || Assets[tran_code] = 333, Assets[process_period_date] = mostRecentMonth ) RETURN earnings

Last Expenses = VAR mostRecentMonth = LASTDATE(Assets[process_period_date]) VAR lastExpense = CALCULATE( SUM(Assets[amount]), Assets[tran_code] = 401 || Assets[tran_code] = 402 || Assets[tran_code] = 403 || Assets[tran_code] = 404 || Assets[tran_code] = 405 || Assets[tran_code] = 406 || Assets[tran_code] = 407 || Assets[tran_code] = 408 || Assets[tran_code] = 409 || Assets[tran_code] = 410 || Assets[tran_code] = 411 || Assets[tran_code] = 433, Assets[process_period_date] = mostRecentMonth ) RETURN lastExpense

Last Transfer Total = VAR mostRecentMonth = LASTDATE(Assets[process_period_date]) VAR transferIn = CALCULATE( SUM(Assets[amount]), Assets[tran_code] = 201 || Assets[tran_code] = 209, Assets[process_period_date] = mostRecentMonth ) VAR transferOut = CALCULATE( SUM(Assets[amount]), Assets[tran_code] = 601 || Assets[tran_code] = 609, Assets[process_period_date] = mostRecentMonth ) return transferIn + transferOut

Re: Running totals are taking too long and the visual exceeds time limit

AnthonyGenovese — Fri, 11 Aug 2023 15:16:02 GMT

Why not just do this? You should be able to put all your codes in an IN statement, or a long OR block. That will reduce some overhead and potential blocking. Also, if you have a dimension for asset transaction codes and have a realtionship to the asset table, you could create an attribute such as [Tran Code Type] "Transfer IN", "Transfer Out" etc and then just filter on those.

CALCULATE( SUM(Assets[amount]), Assets[tran_code] = { 401,402,ALL YOUR CODES} Assets[process_period_date] = mostRecentMonth )

Also, is there a filter on Assets[Process_period_date]? Is the measure displaying data correctly? Because I would think you would need to remove the filter before doing the = mostRecentMonth

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Re: Running totals are taking too long and the visual exceeds time limit

CarlSagan — Fri, 11 Aug 2023 17:28:08 GMT

I don't think using the IN operator is going to make that much of a difference since the IN is translated into the multiple OR expression anyway. So I re-created the data model in a different project and am doing the same exact calculations and I have it working.

The more I've been playing around with it, it seems to be that the bottle neck is actually bringing in related columns from different tables. I think, how can I tell using the optimizer?