Running out of memory after adding a seemingly simple step

[AlB]

Hi all,

I ran into a seemingly strange issue: a calculated column that makes the system run out of memory when I add a seemingly simple step. There are more tables in the model but the relevant ones for the calculated column that makes the report break are:

1. Cover_Causes (400K rows, one of the columns with unique identifiers)

2. Fee_rules (20K rows)

3. Expense _Types (200K rows)

The calculated column is in the Cover_Causes table. The code is below (at the very end). It could be more efficient but I am interested in understanding the step that apparently causes the system to run out of memory. Basically the calc column tries to find, for each row in Cover_Causes, the matching fee(s). For this, it checks the different values of a claim (in each row) and looks at the fee definitions.  At the beginning, it builds "VAR t_consolidated" a consolidated version of the Fee_rules table (results in one fee definition per row, the original one has this info disseminated over several rows). It then filter that t_consolidated and what remains is the fee definitions that comply with the different fields in the row of Cover_causes

The following step seems to be the culprit:

            RETURN
                IF (
                    ISBLANK ( [Logs_operator] ) && ISBLANK ( [GOPs Placed_operator] )&& ISBLANK ( [Medical Journals_operator] ),
                    TRUE (),
                    ( NOT ISBLANK ( [Logs_operator] ) && LogsOp_Check_ )
                        || ( NOT ISBLANK ( [GOPs Placed_operator] ) && GOPsOp_Check_ )
                        || ( NOT ISBLANK ( [Medical Journals_operator] ) && MedJourOp_Check_ )
                )​

In particular the last part, the second argument in the IF( ). We are checking if any of the three rules, if non-blank, is met. The conditions are calculated above this part in VARS. If I limit the second argument of the IF( ) to

      (NOT ISBLANK ( [Logs_operator] ) && LogsOp_Check_ )

it takes a few seconds to execute. If I add a second condition with an OR, it takes a couple of minutes, but it finishes: 

( NOT ISBLANK ( [Logs_operator] ) && LogsOp_Check_ ) || ( NOT ISBLANK ( [GOPs Placed_operator] ) && GOPsOp_Check_ )

but if I add the third condition (as you see in the full code below) it explodes and runs out of memory:

( NOT ISBLANK ( [Logs_operator] ) && LogsOp_Check_ ) || ( NOT ISBLANK ( [GOPs Placed_operator] ) && GOPsOp_Check_ )

|| ( NOT ISBLANK ( [Medical Journals_operator] ) && MedJourOp_Check_ )  

As you can see, each one of those steps does not seem to add a lot of complexity, it just checks if a field is blank and  ANDs it with a VAR that, again, has simple logical operations on fields. The code can be improved significantly and we have already made changes to eliminate the issue, but I am still wondering why the addition of a seemingly simple operation would trigger such behavior and cause the system to run out of memory.

ANYONE ANY IDEAS?

Many thanks

I'll call on the cavalry without further ado

@Zubair_Muhammad , @Greg_Deckler , @MFelix , @ImkeF , @TomMartens , @parry2k , @GilbertQ , @edhans , @amitchandak 

Calc column =
VAR t_consolidated =
    ADDCOLUMNS (
        CALCULATETABLE (
            DISTINCT ( 'Fee Rules'[ft_fee_defname] ),
            FILTER ( 'Fee Rules', 'Fee Rules'[Customer_cust] = Cover_Causes[Customer] )
        ),
        "Amount Base", CALCULATE ( MAX ( 'Fee Rules'[amount_base] ) ),
        "Customer", CALCULATE ( MAX ( 'Fee Rules'[Customer] ) ),
        "Case Type", CALCULATE ( MAX ( 'Fee Rules'[Case Type] ) ),
        "Expense Type", CALCULATE ( MAX ( 'Fee Rules'[ft_exp_typename] ) ),
        "Cover Cause", CALCULATE ( MAX ( 'Fee Rules'[Cover Cause] ) ),
        "GOPs Placed_operator", CALCULATE ( MAX ( 'Fee Rules'[GOPs Placed_operator] ) ),
        "Medical Journals_operator", CALCULATE ( MAX ( 'Fee Rules'[Medical Journals_operator] ) ),
        "Logs_operator", CALCULATE ( MAX ( 'Fee Rules'[Logs_operator] ) ),
        "Zero_Value_Claim", CALCULATE ( MAX ( 'Fee Rules'[ft_zero_value_claimsname] ) ),
        "Cause", CALCULATE ( MAX ( 'Fee Rules'[Possible causes] ) ),
        "Num GOPs Placed_operator",
        VAR temp_operator =
            CALCULATE ( MAX ( 'Fee Rules'[GOPs Placed_operator] ) )
        RETURN
            IF (
                NOT ISBLANK ( temp_operator ),
                CALCULATE (
                    MAX ( 'Fee Rules'[ft_number] ),
                    'Fee Rules'[GOPs Placed_operator] = temp_operator
                )
            ),
        "Num Logs_operator",
        VAR temp_operator =
            CALCULATE ( MAX ( 'Fee Rules'[Logs_operator] ) )
        RETURN
            IF (
                NOT ISBLANK ( temp_operator ),
                CALCULATE (
                    MAX ( 'Fee Rules'[ft_number] ),
                    'Fee Rules'[Logs_operator] = temp_operator
                )
            ),
        "Num Medical Journals_operator",
        VAR temp_operator =
            CALCULATE ( MAX ( 'Fee Rules'[Medical Journals_operator] ) )
        RETURN
            IF (
                NOT ISBLANK ( temp_operator ),
                CALCULATE (
                    MAX ( 'Fee Rules'[ft_number] ),
                    'Fee Rules'[Medical Journals_operator] = temp_operator
                )
            )
    )
VAR t_filtered =
    FILTER (
        t_consolidated,
        [Case Type] = Cover_Causes[Case_Typen]
            && (
                VAR Position_ =
                    SEARCH ( Cover_Causes[ft_causename], [Cause], 1, 0 )
                VAR Position_afterSubstring_ =
                    Position_ + LEN ( Cover_Causes[ft_causename] )
                RETURN
                    IF (
                        Position_ <= 0,
                        FALSE (),
                        (
                            Position_afterSubstring_ > LEN ( [Cause] )
                                || MID ( [Cause], Position_afterSubstring_, 1 ) = UNICHAR ( 10 )
                        )
                    )
                    || ISBLANK ( [Cause] )
            )
            && [Cover Cause] = Cover_Causes[ft_covername]
            && [Zero_Value_Claim] = Cover_Causes[ZeroValueClaims]
            && (
                [Expense Type]
                    IN CALCULATETABLE (
                        DISTINCT ( Expense_types[ft_expense_typename] ),
                        FILTER ( ALL ( Expense_types ), Expense_types[CaseID] = Cover_Causes[CaseID] )
                    )
                    || ISBLANK ( [Expense Type] )
            )
            &&
            VAR LogsOp_Check_ =
                AND (
                    [Logs_operator] = "Less than",
                    Cover_Causes[Nr Spec Activities] < [Num Logs_operator]
                )
                    || AND (
                        [Logs_operator] = "More than",
                        Cover_Causes[Nr Spec Activities] > [Num Logs_operator]
                    )
            VAR GOPsOp_Check_ =
                AND (
                    [GOPs Placed_operator] = "Less than",
                    Cover_Causes[Nr_GOPS] < [Num GOPs Placed_operator]
                )
                    || AND (
                        [GOPs Placed_operator] = "More than",
                        Cover_Causes[Nr_GOPS] > [Num GOPs Placed_operator]
                    )
            VAR MedJourOp_Check_ =
                AND (
                    [Medical Journals_operator] = "Less than",
                    Cover_Causes[Nr_Assessments] < [Num Medical Journals_operator]
                )
                    || AND (
                        [Medical Journals_operator] = "More than",
                        Cover_Causes[Nr_Assessments] > [Num Medical Journals_operator]
                    )
            RETURN
                IF (
                    ISBLANK ( [Logs_operator] ) && ISBLANK ( [GOPs Placed_operator] )
                        && ISBLANK ( [Medical Journals_operator] ),
                    TRUE (),
                    ( NOT ISBLANK ( [Logs_operator] ) && LogsOp_Check_ )
                        || ( NOT ISBLANK ( [GOPs Placed_operator] ) && GOPsOp_Check_ )
                        || ( NOT ISBLANK ( [Medical Journals_operator] ) && MedJourOp_Check_ )
                )
    )
VAR fee_definition_max_amount =
    MAXX ( t_filtered, [Amount Base] )
VAR t_filtered_max_amount =
    FILTER ( t_filtered, [Amount Base] = fee_definition_max_amount )
RETURN
    VAR separator1_ = ", "
    VAR separator2_ =
        UNICHAR ( 10 ) //Use this if you want each fee_definition in a new line instead of separated by ", "
    RETURN
        CONCATENATEX ( t_filtered_max_amount, [ft_fee_defname], separator2_ )
//List all definitions that meet the requirements and have the maximum amount

[ImkeF]

Hi @AlB ,

I've encountered oom errors in DAX before and circumvented them by moving the logic to the query editor:  https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/

There I could rund the logic on much smaller partitions which didn't use as much memory than in DAX.

I believe the key for it lies in the very first steps of your DAX here:

and the second "partition" here:

In every row of your table, the whole source tables are referenced and then the filter down to the relevant customer is applied. 

In a complex code like yours with many if statements, the DAX language cannot play out its strengths.

So I could imagine that it would run faster (if done correctly) in M.

[GilbertQ]

That is some great input @ImkeF 

What I would explain is that when you create a calculated column using DAX, the computation of this column will not be compressed. This will mean it can consume a lot of memory.

Looking at your DAX logic there are a lot of temp tables that have to be built in order to satisfy the VAR that you have created. 

For Example doing a DISTINCT it has to iterate over the entire table to find those distinct values (Which creates a temp in memory table that is uncompressed)

It is possible that it is happening multiple times and possibly based on your DAX it has to persist the table in memory (uncompressed) and that is why you are running out of memory.

As @ImkeF  suggested I would look to doing this in the Power Query Editor, where it is not only more efficient but will allow the resultset to be compressed when it gets transformed into the dataset.