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

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'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