Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

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 

4 REPLIES 4
changqing
Resolver II
Resolver II

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
Super User
Super User

@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
Super User
Super User

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.