Calculation causes "not enough memory" error

[Jarecki]

Hi,

I hope someone will be able to help me with performance issue.

I'm using PowerBI Desktop RS (May 2023) based on Tabular Model.

Fact table contains only 24 rows (here is just a pice of this table):

Problematic measure looks like this:

_test = 
VAR __Category = IF(SELECTEDVALUE(Category[Category_Group]) = "Standard Cost", 6, 12)
VAR __Total =
    CALCULATE(
        [_SumOfCol_n1]
            - [_SumOfCol_49]
            - [_SumOfCol_9]
            - [_SumOfCol_45]
            - [_SumOfCol_50]
        , V_Order_Cost_Breakdown_Pivot_ProfitabilityRep[Attribute_Nr] = __Category
        , ALL(Category)
    )
VAR __Result =
    SWITCH(
        SELECTEDVALUE(Category[Category_ShortDesc]),
        "Total", __Total,
        "1000SQM", DIVIDE(__Total, [_SumCOL_NetSqm]) * 1000,
        "1000KG", DIVIDE(__Total, [_SumCOL_OrderedWght]) * 1000,
        "1000PCS", DIVIDE(__Total, [_SumCOL_OrderedQty]) * 1000
    )
RETURN
    __Result

The goal of this measure is to get Value from row where Attribute nr is 6 or 12 (depends of slicer selection) for each Order.

When I'm adding this measure to empty table it's working fine.

Problem starst when I'm adding fields from other Dimensions table.

So this works fine (script generated by PowerBI Performance analyzer):

DEFINE
    MEASURE '!Measures'[_test] =
        (
            VAR __Category =
                IF ( SELECTEDVALUE ( Category[Category_Group] ) = "Standard Cost", 6, 12 )
            VAR __Total =
                CALCULATE (
                    [_SumOfCol_n1] - [_SumOfCol_49] - [_SumOfCol_9] - [_SumOfCol_45] - [_SumOfCol_50],
                    V_Order_Cost_Breakdown_Pivot_ProfitabilityRep[Attribute_Nr] = __Category,
                    ALL ( Category )
                )
            VAR __Result =
                SWITCH (
                    SELECTEDVALUE ( Category[Category_ShortDesc] ),
                    "Total", __Total,
                    "1000SQM", DIVIDE ( __Total, [_SumCOL_NetSqm] ) * 1000,
                    "1000KG", DIVIDE ( __Total, [_SumCOL_OrderedWght] ) * 1000,
                    "1000PCS", DIVIDE ( __Total, [_SumCOL_OrderedQty] ) * 1000
                )
            RETURN
                __Result 
        )
    VAR __DS0FilterTable =
        TREATAS ( { "Standard Cost" }, 'Category'[Category_Group] )
    VAR __DS0FilterTable2 =
        TREATAS ( { "1000KG" }, 'Category'[Category_ShortDesc] )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL (
                ROLLUPGROUP (
                    'V_Order_Cost_Breakdown_Pivot_ProfitabilityRep'[LINK_Order],
                    'V_D_Customer'[LINK_Customer]
                ),
                "IsGrandTotalRowTotal"
            ),
            __DS0FilterTable,
            __DS0FilterTable2,
            "_test", '!Measures'[_test]
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            502,
            __DS0Core,
            [IsGrandTotalRowTotal], 0,
            'V_Order_Cost_Breakdown_Pivot_ProfitabilityRep'[LINK_Order], 1,
            'V_D_Customer'[LINK_Customer], 1
        )

EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'V_Order_Cost_Breakdown_Pivot_ProfitabilityRep'[LINK_Order],
    'V_D_Customer'[LINK_Customer]

And this not:

DEFINE
    MEASURE '!Measures'[_test] =
        (
            VAR __Category =
                IF ( SELECTEDVALUE ( Category[Category_Group] ) = "Standard Cost", 6, 12 )
            VAR __Total =
                CALCULATE (
                    [_SumOfCol_n1] - [_SumOfCol_49] - [_SumOfCol_9] - [_SumOfCol_45] - [_SumOfCol_50],
                    V_Order_Cost_Breakdown_Pivot_ProfitabilityRep[Attribute_Nr] = __Category,
                    ALL ( Category )
                )
            VAR __Result =
                SWITCH (
                    SELECTEDVALUE ( Category[Category_ShortDesc] ),
                    "Total", __Total,
                    "1000SQM", DIVIDE ( __Total, [_SumCOL_NetSqm] ) * 1000,
                    "1000KG", DIVIDE ( __Total, [_SumCOL_OrderedWght] ) * 1000,
                    "1000PCS", DIVIDE ( __Total, [_SumCOL_OrderedQty] ) * 1000
                )
            RETURN
                __Result 
        )
    VAR __DS0FilterTable =
        TREATAS ( { "Standard Cost" }, 'Category'[Category_Group] )
    VAR __DS0FilterTable2 =
        TREATAS ( { "1000KG" }, 'Category'[Category_ShortDesc] )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL (
                ROLLUPGROUP (
                    'V_Order_Cost_Breakdown_Pivot_ProfitabilityRep'[LINK_Order],
                    'V_D_Customer'[LINK_Customer],
                    'V_D_Location'[LINK_Location]
                ),
                "IsGrandTotalRowTotal"
            ),
            __DS0FilterTable,
            __DS0FilterTable2,
            "_test", '!Measures'[_test]
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            502,
            __DS0Core,
            [IsGrandTotalRowTotal], 0,
            'V_Order_Cost_Breakdown_Pivot_ProfitabilityRep'[LINK_Order], 1,
            'V_D_Customer'[LINK_Customer], 1
        )

EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'V_Order_Cost_Breakdown_Pivot_ProfitabilityRep'[LINK_Order],
    'V_D_Customer'[LINK_Customer]

My suspection is part of measure: ALL ( Category ), but I don't know how may I replace it

[tamerj1]

Hi @Jarecki 

try to replace 

SELECTEDVALUE ( Category[Category_Group] )
by

MAXX ( SUMMARIZE ( FactTable, Category[Category_Group] ), Category[Category_Group] )

and replace SELECTEDVALUE ( Category[Category_ShortDesc] )

by

MAXX ( SUMMARIZE ( FactTable, Category[Category_ShortDesc] ), Category[Category_ShortDesc] )

[Jarecki]

Hi,
thanks for quick reply.

So I made a change regarding to your suggestions, but result is the same.
When I'm trying to add another field from different related table I get "not enought memory" error

_test = 
VAR __Category = 
            IF(MAXX(SUMMARIZE ( V_Order_Cost_Breakdown_Pivot_ProfitabilityRep
                                , Category[Category_Group] 
                            )
                    , Category[Category_Group] 
                ) = "Standard Cost", 6, 12
            )

VAR __Total =
    CALCULATE(
        [_SumOfCol_n1]
            - [_SumOfCol_49]
            - [_SumOfCol_9]
            - [_SumOfCol_45]
            - [_SumOfCol_50]
        , V_Order_Cost_Breakdown_Pivot_ProfitabilityRep[Attribute_Nr] = __Category
        , ALL(Category)
    )
VAR __Result =
    SWITCH(
        MAXX(SUMMARIZE(V_Order_Cost_Breakdown_Pivot_ProfitabilityRep
                        , Category[Category_ShortDesc] 
            )
            , Category[Category_ShortDesc] 
        ),
        "Total", __Total,
        "1000SQM", DIVIDE(__Total, [_SumCOL_NetSqm]) * 1000,
        "1000KG", DIVIDE(__Total, [_SumCOL_OrderedWght]) * 1000,
        "1000PCS", DIVIDE(__Total, [_SumCOL_OrderedQty]) * 1000
    )
RETURN
    __Result

[tamerj1]

@Jarecki 

Try the following 

Return
If (
Not isempty ( V_Order_Cost_Breakdown_Pivot_ProfitabilityRep ), __Result)

[Jarecki · Accepted solution]

Thanks for your help.

I figured out different solution.

Instead of using [ALL (Category)] I used [REMOVEFILTERS(Category)] and magically performance has been fixed and now everything works as I expected.

Now my measure look like this:

_test = 
    VAR __Total_STD = 
        CALCULATE([_SumOfCol_n1] - [_SumOfCol_49] - [_SumOfCol_9] - [_SumOfCol_45] - [_SumOfCol_50]
            , V_Order_Cost_Breakdown_Pivot_ProfitabilityRep[Attribute_Nr] = 6
            , REMOVEFILTERS(Category)
        )
    VAR __Total_QUO = 
        CALCULATE([_SumOfCol_n1] - [_SumOfCol_49] - [_SumOfCol_9] - [_SumOfCol_45] - [_SumOfCol_50]
            , V_Order_Cost_Breakdown_Pivot_ProfitabilityRep[Attribute_Nr] = 12
            , REMOVEFILTERS(Category)
        )
    VAR __Total = SWITCH(SELECTEDVALUE(Category[Category_Group_Nr])
                        , 1 , __Total_STD
                        , __Total_QUO
                )
    VAR __Result =
        SWITCH(SELECTEDVALUE(Category[Category_ShortDesc])
            , "Total", __Total
            , "1000SQM", DIVIDE(__Total, [_SumCOL_NetSqm]) * 1000
            , "1000KG", DIVIDE(__Total, [_SumCOL_OrderedWght]) * 1000
            , "1000PCS", DIVIDE(__Total, [_SumCOL_OrderedQty]) * 1000
        )
RETURN
    __Result