topic Re: Calculation causes "not enough memory" error in DAX Commands and Tips

Calculation causes "not enough memory" error

Jarecki — Tue, 09 Apr 2024 21:18:27 GMT

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

Re: Calculation causes "not enough memory" error

tamerj1 — Tue, 09 Apr 2024 22:06:21 GMT

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] )

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

Re: Calculation causes "not enough memory" error

Jarecki — Wed, 10 Apr 2024 07:14:06 GMT

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

Re: Calculation causes "not enough memory" error

tamerj1 — Wed, 10 Apr 2024 13:46:11 GMT

Jarecki

Try the following

Return
If (
Not isempty ( V_Order_Cost_Breakdown_Pivot_ProfitabilityRep ), __Result)

Re: Calculation causes "not enough memory" error

Jarecki — Thu, 11 Apr 2024 08:25:44 GMT

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