Forum Discussion

Jarecki's avatar
Jarecki
Regular Visitor
2 years ago
Solved

Calculation causes "not enough memory" error

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 tab...
  • Jarecki's avatar
    Jarecki
    2 years ago

    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
Jarecki's avatar
Jarecki
Regular Visitor
2 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
2 years ago

Jarecki 

Try the following 

Return
If (
Not isempty ( V_Order_Cost_Breakdown_Pivot_ProfitabilityRep ), __Result)

  • Jarecki's avatar
    Jarecki
    Regular Visitor
    2 years ago

    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
Related content