# Slow measure needs help!

Dear All,

I am an accountant and just started with DAX and power bi/pivot. Currently I am working on a small dataset (44K rows) and it’s a trial balance file. It has 4 years of data by month. Half is from a manual excel file for balance sheet balances and the other half is actual GL transactions in SQL table. Therefore, I need to calculate starting balance and use cumulative GL transactions to add the starting balance to compute current balance. I do have a few dimension tables (depts, grouping, period etc.). And I am trying to recreate a manual file my team maintains and it shows all monthly TB balances by account, dept, mapping etc. It will have approximately 9K rows * 50 columns if I show everything in excel. Period Index 40 is my cut off number between manual TB balance and SQL GL transaction balance. Below is my code and I know it’s far from good DAX, but considering my fact table is only 44K rows, it shouldn’t be this slow in my mind. Currently if I just show account balances with 2 dimensions (dept, grouping), it’s still very fast. When I add more dimensions, it’s insanely slow. I guess showing 9K rows is not good and my STARTING_BALANCE measure needs to rewrite.

[1.Total] is just sum of balances. On and before my cut-off month, it’s the correct balance sheet balance already. After that, I did a rolling sum of GL transactions. For balance sheet, it’s rolling balance of all periods + starting balance. For P&L, it’s rolling sum of current year only + starting balance.

Can someone please share insights what might be the issue and how I should fix this?

Really appreciate all the help!

=
VAR STARTING_BALANCE = CALCULATE( [1. Total], Period[Index] = 40, all(Period) )
VAR CURRENT_BALANCE =IF (
MIN ( Acct[CATE] ) = “BS”,
CALCULATE (
[1. Total],
FILTER (
ALL ( Period ),
Period[Index] <= MAX ( Period[Index] )
&& Period[Index] >= 41
)
)
,
CALCULATE (
[1. Total],
FILTER (
ALL ( Period ),
Period[Index] <= MAX ( Period[Index] )
&& Period[Year_Name] = MAX ( Period[Year_Name] )
&& Period[Index] >= 41
)
)
) + STARTING_BALANCE

VAR RESULT = SWITCH ( [Calc_Divider_Value] , 1, [1. Total], 2, [1. Total], 3, CURRENT_BALANCE)
RETURN RESULT

Can you try something like this

``````=
VAR STARTING_BALANCE = CALCULATE( [1. Total], Period[Index] = 40, ALL(Period) )
VAR MaxIndex = MAX( Period[Index] )
VAR MaxYearName = MAX( Period[Year_Name] )

VAR CURRENT_BALANCE =
CALCULATE (
[1. Total],
FILTER (
ALL ( Period ),
Period[Index] <= MaxIndex
&& Period[Index] >= 41
&& (
MIN ( Acct[CATE] ) <> “BS”
|| ( MIN ( Acct[CATE] ) = “BS” && Period[Year_Name] = MaxYearName )
)
)
) + STARTING_BALANCE

VAR RESULT =
SWITCH (
[Calc_Divider_Value],
1, [1. Total],
2, [1. Total],
3, CURRENT_BALANCE,
BLANK()   // Default case
)

RETURN RESULT

``````