Forum Discussion

Nimai123's avatar
Nimai123
Post Patron
6 years ago

Memory error

We are trying to solve the issue. We got the problem by the following DAX query on the calculated column -

 

Final 2 =

VAR a = CALCULATE(

MAX('stg kronos'[ENDDTM]),

FILTER(

ALLEXCEPT('stg kronos','stg kronos'[EMPLOYEEID],'stg kronos'[PAYCODENAME]),

'stg kronos'[ENDDTM] < EARLIER('stg kronos'[ENDDTM])

)

)

Var b = IF('stg kronos'[ENDREASON]="missedOut",a,'stg kronos'[STARTDTM])

Return IF(b=BLANK(),'stg kronos'[STARTDTM],b)

 

Above query runs perfectly on the sample table but when we run it on the production table, Power Bi throws memory allocation error.

 

We tried to resize VM from 8Gb to 32Gb to 64Gb but we still got the same error.

Also, we increased the cache memory to 54000mb ie 54Gb.

 

Currently, I'm running power bi on VM with 64Gb of Ram and the total row count is 1.2 million.

 

We took help from the support and advisory on which they suggested that the CALCULATE function is the problem here.

 

Sample pbix file and error screenshot

https://drive.google.com/drive/folders/1Wz51npWQ-8gZjgJ_fXUtPbhWYIW_mWdl?usp=sharing

 

Over here I want to calculate a column which will give me the date of the previous record enddate when ENDREASON is missed out and when it is out it will give the startdate.

 

Trying to break the first half of the query to analyze the problem!

 

 

Expected output

 

amitchandak 

Tahreem24 

Need Help

7 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    Nimai123 ,

    Try to increase the Maximum allowed to 16000 under File / Options and Settings / Options / Global / Data Load.  Data Cache Management Options.

     

    • Tahreem24's avatar
      Tahreem24
      Super User
      6 years ago

      Nimai123 ,

      I have opened your file at my side and havent got any such error.

      First thing, there is no issue with CALCULATE. If it's running fine with your one enviornment so how can you say that there is a issue with CALCULATE while running into production ???

      Second thing, there is no issue with DAX. Your issue is related with memeory/cache size. 

      I can successfully run your file and DAX at my side wihtout any error.  (see the below SS)

       

    • Nimai123's avatar
      Nimai123
      Post Patron
      6 years ago

      Thanks Tahreem24  for the response on the thread

       

      We have increased the cache size up to 54000 which is equivalent to 54 GB

       

      The file which you are using contains the sample data and the screenshot for the error from the production database which is causing the memory issue.

       

       

    • sarveshpandey23's avatar
      sarveshpandey23
      Frequent Visitor
      6 years ago

      Hi Tahreem24,

       

      We tried that as well by increasing the cache memory to 54000 Mb but still we are getting the same error.

       

      About the production thing, we are running the DAX query on the table with approx 12 lakhs rows. 

       

      Note - Created a VM with 64Gb Ram and throwing memory allocation error after increasing the cache memory size.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Nimai123 ,

     

    How about modify your formula as below.

    Final 2 =
VAR a =
    CALCULATE (
        MAX ( 'stg kronos'[ENDDTM] ),
        FILTER (
            ALLEXCEPT ( 'stg kronos', 'stg kronos'[EMPLOYEEID], 'stg kronos'[PAYCODENAME] ),
            'stg kronos'[ENDDTM] < EARLIER ( 'stg kronos'[ENDDTM] )
        )
    )
RETURN
    IF (
        'stg kronos'[ENDREASON] = "missedOut"
            && a <> BLANK (),
        a,
        'stg kronos'[STARTDTM]
    )

    Or split it to two cloumns.

    a =
CALCULATE (
    MAX ( 'stg kronos'[ENDDTM] ),
    FILTER (
        ALLEXCEPT ( 'stg kronos', 'stg kronos'[EMPLOYEEID], 'stg kronos'[PAYCODENAME] ),
        'stg kronos'[ENDDTM] < EARLIER ( 'stg kronos'[ENDDTM] )
    )
)

b =
IF (
    'stg kronos'[ENDREASON] = "missedOut"
        && a <> BLANK (),
    a,
    'stg kronos'[STARTDTM]
)

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Nimai123's avatar
      Nimai123
      Post Patron
      6 years ago

      Hey Anonymous 

       

      I tried splitting the columns but still, I am getting the same error, because I guess so the problem lies with the calculate function (the logic is correct) as it is taking more time to load in the backend as shown in the screenshot!

       

      amitchandak 

      Tahreem24 

      Anmolgan 

      az38 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Nimai123  would you mind sharing how you connected your Kronos data? Was it using the REST API an if so how did you do this?