DAX Optimization Help Needed - Measure That Calculates On Each Date Point in a Line Chart

[Lightsong90]

Hi all,

I have a measure that calculates retention, and my goal is to plot retention over time. The measure works as intended, but when I plot it across more than a few date points, the chart loads pretty slowly. For example, I have a line chart displaying retention over the last 30 days, and it takes about 10 seconds to load.

The dimDate[Date] field is on the X-axis of the line chart, and this measure is on the Y-axis. The dimDate table is NOT connected to any other table via relationships, which allows the measure to function the way it does.

Are there ways I can optimize the performance of this measure or perhaps take a different approach to achieve the same result with faster performance?

Here is the measure:

DynamicRetentionRateDay =
VAR SelectedDate = MAX('dimDate'[Date])
VAR SelectedDateMinus367 = SelectedDate - 367
VAR CurrentMembers =
     CALCULATETABLE(
          DISTINCT('Invoice'[membership__c]),
          ALL('Invoice'),
          'Invoice'[CreatedDate] <= SelectedDate,
          'Invoice'[Expiration_Date__c] >= SelectedDate,
          'Invoice'[Membership_Type__c] <> "Trial Membership"
     )
VAR Members367DaysAgo =
     CALCULATETABLE(
          DISTINCT('Invoice'[membership__c]),
          'Invoice'[CreatedDate] <= SelectedDateMinus367,
          'Invoice'[Expiration_Date__c] >= SelectedDateMinus367
     )
VAR ActiveBoth = INTERSECT(CurrentMembers, Members367DaysAgo)
VAR CountActiveBoth = COUNTROWS(ActiveBoth)
VAR CountMembers367DaysAgo = COUNTROWS(Members367DaysAgo)
RETURN
     DIVIDE(CountActiveBoth, CountMembers367DaysAgo)

[johnt75 · Accepted solution]

You could try creating a summary table which pre-calculates the rate for a given day.

Dynamic Retention Rate Table =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
    "Retention rate",
        VAR SelectedDate = [Date]
        VAR SelectedDateMinus367 = SelectedDate - 367
        VAR CurrentMembers =
            CALCULATETABLE (
                DISTINCT ( 'Invoice'[membership__c] ),
                ALL ( 'Invoice' ),
                'Invoice'[CreatedDate] <= SelectedDate,
                'Invoice'[Expiration_Date__c] >= SelectedDate,
                'Invoice'[Membership_Type__c] <> "Trial Membership"
            )
        VAR Members367DaysAgo =
            CALCULATETABLE (
                DISTINCT ( 'Invoice'[membership__c] ),
                'Invoice'[CreatedDate] <= SelectedDateMinus367,
                'Invoice'[Expiration_Date__c] >= SelectedDateMinus367
            )
        VAR ActiveBoth =
            INTERSECT ( CurrentMembers, Members367DaysAgo )
        VAR CountActiveBoth =
            COUNTROWS ( ActiveBoth )
        VAR CountMembers367DaysAgo =
            COUNTROWS ( Members367DaysAgo )
        RETURN
            DIVIDE ( CountActiveBoth, CountMembers367DaysAgo )
)

It would take a while to create the table during data refresh, but that would be invisible to end users.

[Lightsong90]

@johnt75  This seems like a great solution. I am going to give it a try. One question though. This measure is filterable by other dimension tables and end users are utilizing the filters to look at retention across different dimensions. 

If a filter was applied to the original table would it not impact summary table? If so I would need to figure out a solution for that.

[johnt75]

I think it would depend on how many dimensions were being used at the same time, and how many distinct values those dimensions have. It would in theory be possible to generate the cross join of all dimensions and every date you are interested in, but that would become a very big table very quickly if you have a either a lot of dimensions or just a few dimensions with high cardinality.

SQLBI have a great article on generating summary tables at https://www.sqlbi.com/articles/computing-accurate-percentages-with-row-level-security-in-power-bi/ . You might find some useful insights there.