Forum Discussion

zenz's avatar
zenz
Frequent Visitor
2 years ago
Solved

DAX New Column Error - not enough memory

Hi there, 

 

I am using Power BI Desktop: Version: 2.130.930.0 64-bit (June 2024)

 

I have a table (data set) is about 60K records, and i need to create a new column to see which ID is removed by comparing 2 dates' data sets (see raw tables below for exmaples)

 

I tried to create the new column using the DAX (DAX sytac below) in the Table view, but for such a small data set, I keep getting the not enough memory error.

 

Is there a way to increase the memory used by Power Bi? If so, how? 

Or should I create the new column in the "Transform Data" (Power Query Editor or Power Query M)? 

 

 

Dax sytax (this syntax is tested on a smaller data set, and proven working): 

New_Column =
 
VAR _id = 'table'[eID]
VAR date_prior = 'table'[Report_Date] +1
VAR id_prior = CALCULATETABLE(VALUES('table'[eID]), REMOVEFILTERS( 'table' ), 'table'[Report_Date] = date_prior , 'table'[eID] = _id)
VAR id_in_prior = CALCULATE( MAX( 'table'[eID] ), not('table'[eID]) in { id_prior })
VAR max_date = CALCULATE( MAX( 'table'[Report_Date] ), REMOVEFILTERS( 'table' ))
RETURN  
    if( ISBLANK( id_in_prior ), 0,if('table'[Report_Date] = max_date, 0,-1 ))

 

 

the new column is 

  • New_Column = if 'id' exists in only previous date, put -1 on 6/18.
    • example: id = 208

 

here is the raw table :

idbutypedate
20810red6/18/2024
20910red6/18/2024
21010red6/18/2024
21110red6/18/2024
20910red6/19/2024
21020red6/19/2024
21110blue6/19/2024
21210red6/19/2024

 

Here is the final table wanted: 

idbutypedateNew_Column
20810red6/18/2024-1
20910red6/18/20240
21010red6/18/20240
21110red6/18/20240
20910red6/19/20240
21020red6/19/20240
21110blue6/19/20240
21210red6/19/20240

 

id - whole number 

bu - whole number

type - text

date - date

Need Help
Show and Tell
Tips and Tricks
  • mark_endicott's avatar
    mark_endicott
    2 years ago

    zenz - Instead of a column, you could create this as a measure. The DAX needs to change slightly, but the below should work for you:

     

    VAR _id =
    SELECTEDVALUE ( 'Table (7)'[id] )
VAR next_date =
    SELECTEDVALUE ( 'Table (7)'[date] ) + 1
VAR current_date =
    SELECTEDVALUE ( 'Table (7)'[date] )
VAR id_next =
    CALCULATETABLE (
        VALUES ( 'Table (7)'[id] ),
        REMOVEFILTERS ( 'Table (7)' ),
        'Table (7)'[date] = next_date,
        'Table (7)'[id] = _id
    )
VAR max_date =
    CALCULATE ( MAX ( 'Table (7)'[date] ), REMOVEFILTERS ( 'Table (7)' ) )
RETURN
    SWITCH (
        TRUE (),
        _id IN id_next, 0,
        current_date = max_date, 0,
        NOT ( _id ) IN id_next, -1
    )

    Screenshot below shows it's working for me:

    If this works for you, please mark it as the solution. 

  • mark_endicott's avatar
    mark_endicott
    2 years ago

    zenz - Please remember to accept this as the solution, it helps others learning DAX find answers to their problems. 

5 Replies

  • mark_endicott's avatar
    mark_endicott
    Super User
    2 years ago

    zenz - Instead of a column, you could create this as a measure. The DAX needs to change slightly, but the below should work for you:

     

    VAR _id =
    SELECTEDVALUE ( 'Table (7)'[id] )
VAR next_date =
    SELECTEDVALUE ( 'Table (7)'[date] ) + 1
VAR current_date =
    SELECTEDVALUE ( 'Table (7)'[date] )
VAR id_next =
    CALCULATETABLE (
        VALUES ( 'Table (7)'[id] ),
        REMOVEFILTERS ( 'Table (7)' ),
        'Table (7)'[date] = next_date,
        'Table (7)'[id] = _id
    )
VAR max_date =
    CALCULATE ( MAX ( 'Table (7)'[date] ), REMOVEFILTERS ( 'Table (7)' ) )
RETURN
    SWITCH (
        TRUE (),
        _id IN id_next, 0,
        current_date = max_date, 0,
        NOT ( _id ) IN id_next, -1
    )

    Screenshot below shows it's working for me:

    If this works for you, please mark it as the solution. 

    • zenz's avatar
      zenz
      Frequent Visitor
      2 years ago

      mark_endicott , 

       

      thank you so much. You are super start!. 

       

      This should solve another question I had for you. really apprecitate it! 

      • mark_endicott's avatar
        mark_endicott
        Super User
        2 years ago

        zenz - Please remember to accept this as the solution, it helps others learning DAX find answers to their problems. 

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    Hello zenz ,

     

    I believe adjusting memory usage should be in option.

     

    However, I am just sharing my experience when I faced this memory issue before. If you have big data bank with complex calculation, you probably want to slice the data first.

     

    in your id_prior variable, you are comparing 'table'[date] with date_prior and 'table'[id] with _id which both date_prior and eID are variables you have defined before. This makes big performance impact since that DAX will compare every value in those column (this is my understanding from my previous issue, might be wrong though).

     

    I wrote exactly same DAX as yours and got low memory issue as well.

    To solve this matter, in my case, I slice my data first before applying or comparing variable by using SUMMARIZE so it would not do comparison to all data.

     

    But if your data is cleaned, then perhaps adding RAM might be able to help.

     

    But other Power BI expert might have better solution.

     

    Hope this will help you.

    Thank you.

    • zenz's avatar
      zenz
      Frequent Visitor
      2 years ago

      Thanks you Irwan. 

      I am very new to DAX (1 week self taught). Would you mind recompose the DAX code to show me how you would slice the data using SUMMARIZE? Appreciate it!