Not enough memory error with ALLEXCEPT calc

[kumsha1]

Hi,

we have a table visual with 31 columns and the visual renders and displays data in less than a minute, however when we try to add one more measure to this visual then system is propmting below memory error. Can someone pls help if there is any better way to rewrite this measure DAX formula to check MAX for each ID and then get the FLAG status...TIA.

Error:

Measure option 1:

Max Post Date Flag =

IF (

YEAR (

CALCULATE (

MAX ( 'CONSUMPTION CALC'[POST_DATE] ),

ALLEXCEPT ( 'CONSUMPTION CALC', 'CONSUMPTION CALC'[MATERIAL_ID] ),

FILTER ( 'CONSUMPTION CALC', 'CONSUMPTION CALC'[QTY] < 0 ),

'CONSUMPTION CALC'[MVMT_TYPE_ID] <> "551"

)

) > 1980,

"",

"No Consumption"

)

------------------------------

Measure option 2:

Max Post Date New =

VAR _MAXPOSTDATE =

CALCULATE (

MAX ( 'CONSUMPTION CALC'[POST_DATE] ),

ALLEXCEPT ( 'CONSUMPTION CALC', 'CONSUMPTION CALC'[MATERIAL_ID] ),

FILTER ( 'CONSUMPTION CALC', 'CONSUMPTION CALC'[QTY] < 0 ),

'CONSUMPTION CALC'[MVMT_TYPE_ID] <> "551"

)

VAR _FLAG =

IF ( YEAR ( _MAXPOSTDATE ) > 1980, "", "No consumption" )

RETURN

_FLAG

[kumsha1]

Hi @amitchandak ,

Increased the memory of desktop file and rewritten the DAX for this measure but still have same memory issue.. Report published to PRO workspace and PREMIUM P1 workspace also have this issue with below error.

Actual file size is 63 MB, not sure where and why so much of memory being consumed.

[v-easonf-msft]

Hi,  @kumsha1 

Hope these threads are useful.

https://community.powerbi.com/t5/Desktop/Not-Enough-Memory-Space/m-p/2023905 

https://guyinacube.com/2021/05/12/test-your-power-bi-dax-measures-in-dax-studio/ 

https://www.designmind.com/business-intelligence/power-bi-performance-issues/ 

Best Regards,
Community Support Team _ Eason

[amitchandak]

@kumsha1 , Try like

Max Post Date Flag =
IF (
YEAR (
CALCULATE (
MAX ( 'CONSUMPTION CALC'[POST_DATE] ),
ALLEXCEPT ( 'CONSUMPTION CALC', 'CONSUMPTION CALC'[MATERIAL_ID] ),
FILTER ( 'CONSUMPTION CALC', 'CONSUMPTION CALC'[QTY] < 0 && 'CONSUMPTION CALC'[MVMT_TYPE_ID] <> "551"
)
) > 1980,
"",
"No Consumption"
)

Also increase Data management cache under File-> Options and setting -> Options -> data Load

and try