topic Re: This dax code works but takes forever to refresh visual in DAX Commands and Tips

This dax code works but takes forever to refresh visual

plantew1 — Tue, 22 Aug 2023 09:40:00 GMT

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

Re: This dax code works but takes forever to refresh visual

tamerj1 — Tue, 22 Aug 2023 11:20:12 GMT

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

Re: This dax code works but takes forever to refresh visual

plantew1 — Tue, 22 Aug 2023 12:08:34 GMT

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.

Re: This dax code works but takes forever to refresh visual

tamerj1 — Tue, 22 Aug 2023 12:46:10 GMT

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

Re: This dax code works but takes forever to refresh visual

plantew1 — Tue, 22 Aug 2023 12:50:11 GMT

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?

Re: This dax code works but takes forever to refresh visual

tamerj1 — Tue, 22 Aug 2023 12:54:55 GMT

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

Re: This dax code works but takes forever to refresh visual

plantew1 — Tue, 22 Aug 2023 13:08:14 GMT

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

Re: This dax code works but takes forever to refresh visual

tamerj1 — Tue, 22 Aug 2023 13:39:00 GMT

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.

Re: This dax code works but takes forever to refresh visual

plantew1 — Wed, 23 Aug 2023 12:31:56 GMT

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