Iterative Expression Optimization

[MPetramalo214]

Hi everyone.  I'm trying to create a dax query that will accomplish a couple things; but I'm getting timeout errors, so I know that I need to make it more efficient - I just can't find a good way to do that.

Goal: Create a DAX Measure that will calculate the number of surveys that would be expected to go out based on hotel checkouts.

 - I'm currently doing this by creating a summarized table to check for every customer id (~250K entries).  The first column will check if the customer had a reservation check-out on the date we're looking at.  The second column checks if the owner had a reservation check-out in the 30 days prior to date.  We then check if the first column returns true, and the second returns false.  These customers would be expected to have recieved a survey.

EVALUATE
SUMX(
    SUMMARIZE(
        CROSSJOIN(Customers, 'Calendar'),
        Customers[Owner Id],
        "_survey", IF(
            CALCULATE(
                COUNTROWS(Reservations),
                FILTER(
                    ALLEXCEPT(Reservations, Locations[location_name]),
                    Reservations[Owner Id] = SELECTEDVALUE(Customers[Owner Id])
                    && DATEVALUE(Reservations[Check-Out Date]) = MAX('Calendar'[Date])
                )
            ) > 0, 1, 0
        ),
        "_last30", IF(
            CALCULATE(
                COUNTROWS(Reservations),
                FILTER(
                    ALLEXCEPT(Reservations, Locations[location_name]),
                    Reservations[Owner Id] = SELECTEDVALUE(Customers[Owner Id])
                    && DATEVALUE(Reservations[Check-Out Date]) >= (MAX('Calendar'[Date])-30)
                    && DATEVALUE(Reservations[Check-Out Date]) < MAX('Calendar'[Date])
                )
            ) > 0, 0, 1
        )
    ),
    IF([_survey] = 1 && [_last30] = 1, 1, 0)
)

The table above is the result of just the SUMMARIZE expression.  The SUMX function will add one anytime the values are both 1. 

The table below is the SUMX result in Power BI - it prints out the correct value per day; but I have been unsuccessful in summing this table to get a rolling total.

Using the values in the table above, my expected result would be

Date	NPS Surveys Goal (Rolling)
2022-01-01	19
2022-01-02	68
2022-01-03	112
2022-01-04	136
2022-01-05	149
2022-01-06	170
2022-01-07	178
2022-01-08	190
2022-01-09	216

I've tried doing the standard quick measure for running total over time, but that didn't produce correct results.  I also tried a few other methods such as SUMX over the SUMX using the calendar table but the query times-out in DAX Studio; so I'm concerned its an efficiency issue. 

I imagine there is a better way to iterate only a subset of customers, but I'm not sure how I'd get that value.  But that would cut iteration from 250K customers, to a much smaller value.  Preferably to customers who actually checked out that day, but I'm not sure where I'd get that.

[v-easonf-msft]

Hi, @MPetramalo214 

Have you tried the following formula?

Measure1 =
SUMX ( VALUES ( 'Calendar'[DATE] ), [NPS Surveys Goal] )

Best Regards,
Community Support Team _ Eason

[MPetramalo214]

Hi @v-easonf-msft - I did try this formula; but it produced incorrect results.  There were days where the value would go down from day prior; but there should never be an instance where the value was less than day prior.  (You don't ever lose customers who would be eligible for a survey, you just add the new users who are)

[lbendlin]

Simplify the query until it doesn't time out in DAX Studio. Then look at the query plan it produces. Look for excessive number of records.

Try filtering first for the items with the biggest impact (low cardinality) and then add the other filters in order of increased cardinality/reduced impact.