This dax code works but takes forever to refresh visual

[plantew1]

Thanks for giving this a look. I have code that works as I need it to, that generates a calculated table. When I tried to use this table As the basis for a line chart visual with the calendar[date] field as the x-axis, it takes forever to load or refresh. I'm still learning and can't spot what is making it slow. Any insights would be greatly appreciated.

Report Summary: Assigned to District = 

FILTER(
    SELECTCOLUMNS(
        GENERATE(
            'Object: Intake',
            DATESBETWEEN(
                'Calendar Backup'[Date],
                'Object: Intake'[SAO_Assigned_Date__c],
                IF(
                    CONTAINSSTRING( 'Object: Intake'[Status], "4"),
                    TODAY(),
                    IF(
                        NOT( ISBLANK('Object: Intake'[Date_Assigned_to_Workspace__c] ) ),
                        IF(
                            'Object: Intake'[Date_Assigned_to_Workspace__c],
                            'Object: Intake'[Date_Assigned_to_Workspace__c] - 1,
                            'Object: Intake'[SAO_Assigned_Date__c]
                            )
                        ,
                        IF(
                            NOT( ISBLANK('Object: Intake'[Date_Assigned_to_Inspector_s__c]) ),
                            'Object: Intake'[Date_Assigned_to_Inspector_s__c] - 1,
                            IF(
                                NOT( ISBLANK( 'Object: Intake'[ClosedDate] ) ),
                                'Object: Intake'[Intake_Locked_Date__c] - 1,
                                TODAY() + 1
                            )
                        )
                    )
                )
            )
        ),
        "Case Number", 'Object: Intake'[CaseNumber],
        "Begin Date", 'Object: Intake'[SAO_Assigned_Date__c],
        "Status", 'Object: Intake'[Status],
        "Target Status", 'Object: Intake'[Target_Status__c],
        "Days Past Due", 'Object: Intake'[Days Past Due],
        "Past Due", IF(
                        'Object: Intake'[Days Past Due] > 0 && 'Object: Intake'[Target_Status__c] in {"Target Date Passed; Inspection Did Not Happen Yet", "Inspection Initiated; Target Not Met" },
                        TRUE(),
                        FALSE()
        ),
        "Date", 'Calendar Backup'[Date]
        ),
        NOT( ISBLANK( [Begin Date] ) ) &&
        //Remove cancelled intakes
        NOT( CONTAINSSTRING( [Status], "6" ) )
    )

[plantew1]

Thank you for that suggestion. I gave it a try and it didn't seem to impact the time of refresh very much, but after reviewing it probably was the better way of writing my code. Thanks again.

[tamerj1]

@plantew1 
Indeed it is such heavy calculation. 

Try with CALENDAR instead of DATESBETWEEN. Also double check the IF statements, the following statement does not make much sense 

IF (
'Object: Intake'[Date_Assigned_to_Workspace__c],     -- what are you trying to achieve here?
'Object: Intake'[Date_Assigned_to_Workspace__c] - 1,
'Object: Intake'[SAO_Assigned_Date__c]
)

[plantew1]

Yes, good catch on the "workspace" check. It was missing the <= that was cut off on my copy of the code. I will revise.

When you say "Try with CALENDAR instead of DATESBETWEEN" can you expand on what you mean by that (sorry very new to this). How would I change the DateBetween portion as you suggest?

[tamerj1]

@plantew1 

Report Summary: Assigned to District =
SELECTCOLUMNS (
    GENERATE (
        FILTER (
            'Object: Intake',
            'Object: Intake'[SAO_Assigned_Date__c] <> BLANK ()
                && NOT CONTAINSSTRING ( 'Object: Intake'[Status], "6" )
        ),
        CALENDAR (
            'Object: Intake'[SAO_Assigned_Date__c],
            IF (
                CONTAINSSTRING ( 'Object: Intake'[Status], "4" ),
                TODAY (),
                IF (
                    NOT ( ISBLANK ( 'Object: Intake'[Date_Assigned_to_Workspace__c] ) ),
                    IF (
                        'Object: Intake'[Date_Assigned_to_Workspace__c] <= _SomeValue,
                        'Object: Intake'[Date_Assigned_to_Workspace__c] - 1,
                        'Object: Intake'[SAO_Assigned_Date__c]
                    ),
                    IF (
                        NOT ( ISBLANK ( 'Object: Intake'[Date_Assigned_to_Inspector_s__c] ) ),
                        'Object: Intake'[Date_Assigned_to_Inspector_s__c] - 1,
                        IF (
                            NOT ( ISBLANK ( 'Object: Intake'[ClosedDate] ) ),
                            'Object: Intake'[Intake_Locked_Date__c] - 1,
                            TODAY () + 1
                        )
                    )
                )
            )
        )
    ),
    "Case Number", 'Object: Intake'[CaseNumber],
    "Begin Date", 'Object: Intake'[SAO_Assigned_Date__c],
    "Status", 'Object: Intake'[Status],
    "Target Status", 'Object: Intake'[Target_Status__c],
    "Days Past Due", 'Object: Intake'[Days Past Due],
    "Past Due",
        IF (
            'Object: Intake'[Days Past Due] > 0
                && 'Object: Intake'[Target_Status__c]
                IN {
                    "Target Date Passed; Inspection Did Not Happen Yet",
                    "Inspection Initiated; Target Not Met"
                },
            TRUE (),
            FALSE ()
        ),
    "Date", [Date]
)

[plantew1]

I revised the code to get rid of the "if issue". But its giving an error that the "Calendar start date can't be earlier than the calendar end date". I will keep working on that error and see if I can figure out why its happening. Thanks.

Report Summary: Assigned to District = 
SELECTCOLUMNS (
    GENERATE (
        FILTER (
            'Object: Intake',
            'Object: Intake'[SAO_Assigned_Date__c] <> BLANK ()
                && NOT CONTAINSSTRING ( 'Object: Intake'[Status], "6" )
        ),
        CALENDAR (
            MIN('Calendar'[Date]),
            IF (
                CONTAINSSTRING ( 'Object: Intake'[Status], "4" ),
                TODAY (),
                IF (
                    NOT ( ISBLANK ( 'Object: Intake'[Date_Assigned_to_Workspace__c] ) ),
                    IF (
                        'Object: Intake'[Date_Assigned_to_Workspace__c] <= 'Object: Intake'[SAO_Assigned_Date__c],
                        'Object: Intake'[Date_Assigned_to_Workspace__c] - 1,
                        'Object: Intake'[SAO_Assigned_Date__c]
                    ),
                    IF (
                        NOT ( ISBLANK ( 'Object: Intake'[Date_Assigned_to_Inspector_s__c] ) ),
                        'Object: Intake'[Date_Assigned_to_Inspector_s__c] - 1,
                        IF (
                            NOT ( ISBLANK ( 'Object: Intake'[ClosedDate] ) ),
                            'Object: Intake'[Intake_Locked_Date__c] - 1,
                            TODAY () + 1
                        )
                    )
                )
            )
        )
    ),
    "Case Number", 'Object: Intake'[CaseNumber],
    "Begin Date", 'Object: Intake'[SAO_Assigned_Date__c],
    "Status", 'Object: Intake'[Status],
    "Target Status", 'Object: Intake'[Target_Status__c],
    "Days Past Due", 'Object: Intake'[Days Past Due],
    "Past Due",
        IF (
            'Object: Intake'[Days Past Due] > 0
                && 'Object: Intake'[Target_Status__c]
                IN {
                    "Target Date Passed; Inspection Did Not Happen Yet",
                    "Inspection Initiated; Target Not Met"
                },
            TRUE (),
            FALSE ()
        ),
    "Date", [Date]
)

[tamerj1]

@plantew1 
There is no meaning of using MIN('Calendar'[Date]) in this context. 'Object: Intake'[SAO_Assigned_Date__c] should be the column to use I guess. However, this is not the source of the error. Mainly such errors apear when you have blanks with one or more of the date columns referred to in the formula. Perhaps you need to use COALESCE to return analternative value if the date value is blank.

[plantew1 · Accepted solution]

I found my way through on this. I have the revised code below and it loads much faster. Thanks for your guidance. Here was my revision.

CALCULATETABLE(
    GENERATE (
        VALUES ( 'Calendar'[Date] ),
        FILTER (
            CALCULATETABLE('Object: Intake', ALL('Calendar')),
            CONTAINS (
                DATESBETWEEN (
                    'Calendar'[Date],
                    'Object: Intake'[SAO_Assigned_Date__c],
                    IF(
                        CONTAINSSTRING( 'Object: Intake'[Status], "4"),
                        TODAY(),
                        IF(
                            NOT( ISBLANK('Object: Intake'[Date_Assigned_to_Workspace__c] ) ),
                            IF(
                                'Object: Intake'[Date_Assigned_to_Workspace__c] > 'Object: Intake'[SAO_Assigned_Date__c],
                                'Object: Intake'[Date_Assigned_to_Workspace__c] - 1,
                                'Object: Intake'[SAO_Assigned_Date__c]
                                )
                            ,
                            IF(
                                NOT( ISBLANK('Object: Intake'[Date_Assigned_to_Inspector_s__c]) ),
                                'Object: Intake'[Date_Assigned_to_Inspector_s__c] - 1,
                                IF(
                                    NOT( ISBLANK( 'Object: Intake'[ClosedDate] ) ),
                                    'Object: Intake'[Intake_Locked_Date__c] - 1,
                                    TODAY() + 1
                                )
                            )
                        )
                    )
                ),
                [Date], 'Calendar'[Date]
            )
        )
    ),
    NOT( CONTAINSSTRING( 'Object: Intake'[Status], "6" ) ),
    NOT( ISBLANK( 'Object: Intake'[SAO_Assigned_Date__c] ) )
)

[tamerj1]

Hi @plantew1 
Try ti first FILTER the GENERATE

Report Summary: Assigned to District =
SELECTCOLUMNS (
    GENERATE (
        FILTER (
            'Object: Intake',
            'Object: Intake'[SAO_Assigned_Date__c] <> BLANK ()
                && NOT CONTAINSSTRING ( 'Object: Intake'[Status], "6" )
        ),
        DATESBETWEEN (
            'Calendar Backup'[Date],
            'Object: Intake'[SAO_Assigned_Date__c],
            IF (
                CONTAINSSTRING ( 'Object: Intake'[Status], "4" ),
                TODAY (),
                IF (
                    NOT ( ISBLANK ( 'Object: Intake'[Date_Assigned_to_Workspace__c] ) ),
                    IF (
                        'Object: Intake'[Date_Assigned_to_Workspace__c],
                        'Object: Intake'[Date_Assigned_to_Workspace__c] - 1,
                        'Object: Intake'[SAO_Assigned_Date__c]
                    ),
                    IF (
                        NOT ( ISBLANK ( 'Object: Intake'[Date_Assigned_to_Inspector_s__c] ) ),
                        'Object: Intake'[Date_Assigned_to_Inspector_s__c] - 1,
                        IF (
                            NOT ( ISBLANK ( 'Object: Intake'[ClosedDate] ) ),
                            'Object: Intake'[Intake_Locked_Date__c] - 1,
                            TODAY () + 1
                        )
                    )
                )
            )
        )
    ),
    "Case Number", 'Object: Intake'[CaseNumber],
    "Begin Date", 'Object: Intake'[SAO_Assigned_Date__c],
    "Status", 'Object: Intake'[Status],
    "Target Status", 'Object: Intake'[Target_Status__c],
    "Days Past Due", 'Object: Intake'[Days Past Due],
    "Past Due",
        IF (
            'Object: Intake'[Days Past Due] > 0
                && 'Object: Intake'[Target_Status__c]
                IN {
                    "Target Date Passed; Inspection Did Not Happen Yet",
                    "Inspection Initiated; Target Not Met"
                },
            TRUE (),
            FALSE ()
        ),
    "Date", 'Calendar Backup'[Date]
)