Memory error

[Nimai123]

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 

[apaulso9]

@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?

[v-jayw-msft]

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]

Hey @v-jayw-msft 

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 

[Tahreem24]

@Nimai123 ,

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

[sarveshpandey23]

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.

[Nimai123]

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.

[Tahreem24]

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