Super Slow Measure for small dataset

[powerquerytony1]

slow DAX measures (need help to rewrite it!)

yesterday

Hi there, 

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 balance sheet 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

[Anonymous]

Hi @powerquerytony1 ,

You can update the formula of measure as below and check if it can run smoothly...

Your measure =
VAR INDEX =
    MAX ( Period[Index] )
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] >= 41 && Period[Index] <= INDEX )
        ),
        CALCULATE (
            [1. Total],
            FILTER (
                ALL ( Period ),
                Period[Index] >= 41
                    && Period[Index] <= INDEX
                    && Period[Year_Name] = MAX ( Period[Year_Name] )
            )
        )
    ) + STARTING_BALANCE
VAR RESULT =
    IF (
        [Calc_Divider_Value] IN { 1, 2 },
        [1. Total],
        IF ( [Calc_Divider_Value] = 3, CURRENT_BALANCE )
    )
RETURN
    RESULT

In addition, you can refer the following links to optimize your DAX...

DAX Best Practice Guide

Optimizing DAX Video Course

How to Optimize Dax Queries in Power BI

Use Performance Analyzer to examine report element performance (can see and record logs that measure how each of your report elements performs when users interact with them, and which aspects of their performance are most (or least) resource intensive.)

Best Regards