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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors