NEED HELP : Calculated Column refreshes rather slowly and occasionally experiences memory problems.

[Venkateswara_ra]

In my AAS Cube (Live connection to BI), I constructed a calculated column, however the calculated column is taking too long.

This is the query that I tried to optimise in the end.

What is wrong with my query , please?

I have used 2 tables in calculated column

One is Case table and another is  header table 

Rowcount of case table is = 3819718

Rowcount of header table is = 12957845

calculated column =
VAR ID = Case[Id]
VAR Type = Case[Type]
VAR CreationDate = Case[Creation Date]
VAR LastModified = Case[Last Modified On Date]
VAR FNOL =
LOOKUPVALUE(
'Header'[Reported Date],
'Header'[id], ID
)
RETURN
SWITCH(
TRUE(),
Type
IN {
"Absent",
"Leave"
},
CALCULATE(
SUM( Date[Work] ),
Date[Date] >= CreationDate
&& Date[Date] <= LastModified
),
Type = "Present",
CALCULATE(
SUM( Date[Work] ),
Date[Date] >= FNOL
&& Date[Date] <= LastModified
),
BLANK()
)

Thanks in advance 

[changqing]

Hi @Venkateswara_ra ,

Please try this:

calculated column =
VAR ID = Case[Id]
VAR Type = Case[Type]
VAR CreationDate = Case[Creation Date]
VAR LastModified = Case[Last Modified On Date]
VAR FNOL =
    LOOKUPVALUE ( 'Header'[Reported Date], 'Header'[id], ID )
RETURN
    SWITCH (
        TRUE (),
        Type = "Present",
            CALCULATE (
                SUM ( Date[Work] ),
                Date[Date] >= FNOL
                    && Date[Date] <= LastModified
            ),
        NOT Type IN { "Absent", "Leave" }, BLANK (),
        CALCULATE (
            SUM ( Date[Work] ),
            Date[Date] >= CreationDate
                && Date[Date] <= LastModified
        )
    )

Best Regards,
changqing

[tamerj1]

@Venkateswara_ra 
I don't see much to be done here. If what you call in you code 'Date' is just a date table then this should be super fast as the date table is a very small table. It could be the LOOKUPVALUE which causing the problem. So I suggest to tray with TREATAS. Please try

calculated column =
VAR ID = Case[Id]
VAR Type = Case[Type]
VAR CreationDate = Case[Creation Date]
VAR LastModified = Case[Last Modified On Date]
VAR FNOL =
    CALCULATE (
        SELECTEDVALUE ( 'Header'[Reported Date] ),
        TREATAS ( { ID }, 'Header'[id] )
    )
VAR DateWork =
    FILTER ( 'Date', 'Date'[Date] <= LastModified )
RETURN
    SWITCH (
        TRUE (),
        Type IN { "Absent", "Leave" }, SUMX ( FILTER ( DateWork, 'Date'[Date] >= CreationDate ), 'Date'[Work] ),
        Type = "Present", SUMX ( FILTER ( DateWork, 'Date'[Date] >= FNOL ), 'Date'[Work] )
    )

[tamerj1]

Hi @Venkateswara_ra 

what does this variable supposed to calculate:

VAR FNOL =
LOOKUPVALUE(
'Header'[Reported Date],
'Header'[id], ID
)

becuase if there is only one date for each id then it is the date of the current row. Please clarify this point

[Venkateswara_ra]

Hi @tamerj1 

Due to model difficulties, the case table doesn't have a [Reported Date] column and we are unable to establish active relationships between these tables, thus I am saving [Reported Date] in that variable using the LOOKUPVALUE function based on the ID column.

Yes there is only one date for each id in the header table 

Header table is DIM table and case table is fact