topic Re: NEED HELP : Calculated Column refreshes rather slowly and occasionally experiences memory problems. in DAX Commands and Tips

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

Venkateswara_ra — Sun, 07 Aug 2022 17:06:40 GMT

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

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

tamerj1 — Sun, 07 Aug 2022 19:49:56 GMT

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

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

Venkateswara_ra — Mon, 08 Aug 2022 06:56:51 GMT

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

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

tamerj1 — Mon, 08 Aug 2022 07:13:46 GMT

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

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

changqing — Wed, 10 Aug 2022 10:15:18 GMT

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