Help with performance of Calculated Table

[mastone]

I have been looking at performance using SQL Profiler connected up through one of my powerbi.com XMLNA endpoints.  Trying to find the bottleneck I ran across one calculated table that is 3 times the duration of anything else in the report.  It takes almost 20 minutes to process.  I am not great at dax, and so I am certain what I have done can be done in another way to make it more performant.  Here is what I have.

I have a calcuated table defined as:

Mobile Not Screening Table = 
SELECTCOLUMNS(
    FILTER(Sets, Sets[IsMobile]),
    "MobileSetId", Sets[Id],
    "ContactId", Sets[ContactId],
    "Date", Sets[Date],
    "ChecklistId", Sets[ChecklistId],
    "StationScreening", MAXX(
                    TOPN(
                        1, 
                        FILTER(
                            ALL(Sets), 
                            Sets[ContactId] = EARLIER([ContactId]) &&
                            Sets[DateTime] > EARLIER([DateTime]) &&
                            DATEDIFF(EARLIER([DateTime]), Sets[DateTime], MINUTE) <= RELATED(Checklists[Prescreen Minutes])
                        ),
                        Sets[DateTime], DESC
                    ),
                    Sets[Id]
                ),
    "SystemId", RELATED(Systems[Id])

)

The bottleneck is the stationscreening column. Can this be rewritten in a more performant way?

What that column is doing is this:

For any Set that is marked as Mobile (filter placed on table itself), it looks for the id of different Set that comes after the current Set but is within X number of minutes of the current Set. I then grab the one that is closest to the current set (i.e. the order by)

This is all within one table/query called Set so there are no data relationships involved aside from grabbing the "X number of minutes" from the checklists table.

There are millions of Sets but very few records that are marked with [IsMobile]. For december 2023, there were ~250 [IsMobile] Sets.

This calculated table is very basic aside form that one column. Would it be more performant to have that column as a measure on the sets table? I believe I tried to convert that into a measure but wasn't able to figure out how to do it.

Any suggestions to speed this up?

[mastone]

Thank you for the response.  I didn't see this in time.  Ultimately what I did was push the query back to SQL server where those items are indexed and the calculation is fairly responsive.  It cut my load time down from 6 hours for a full year to 9 minutes.

[DataInsights]

@mastone,

Try this calculated table. It uses ADDCOLUMNS (an iterator), and variables instead of EARLIER. You could experiment with CALCULATETABLE instead of FILTER for the ADDCOLUMNS table argument (line 4).

Mobile Not Screening Table =
SELECTCOLUMNS (
    ADDCOLUMNS (
        FILTER ( Sets, Sets[IsMobile] ),
        "SystemId", RELATED ( Systems[Id] ),
        "StationScreening",
            VAR vContactId = Sets[ContactId]
            VAR vDateTime = Sets[DateTime]
            RETURN
                MAXX (
                    TOPN (
                        1,
                        FILTER (
                            ALL ( Sets ),
                            Sets[ContactId] = vContactId
                                && Sets[DateTime] > vDateTime
                                && DATEDIFF ( vDateTime, Sets[DateTime], MINUTE )
                                    <= RELATED ( Checklists[Prescreen Minutes] )
                        ),
                        Sets[DateTime], DESC
                    ),
                    Sets[Id]
                )
    ),
    "MobileSetId", Sets[Id],
    "ContactId", Sets[ContactId],
    "Date", Sets[Date],
    "ChecklistId", Sets[ChecklistId],
    "StationScreening", [StationScreening],
    "SystemId", [SystemId]
)