Calculating % Change When Client Has 2X Specific Surveys

[samdep]

Hi Community!

I have client survey data and have calculated a % change over ~90 days, from Survey 1 to Survey 3 -- my DAX below:

RATE_CHANGE =

VAR SURVEY_1 = CALCULATE(AVERAGE('Assessment'[Client_Rating__c]),'Assessment'[Client_Rating__c] = "Survey 1")

VAR SURVEY_3= CALCULATE(AVERAGE('Assessment'[Client_Rating__c]),'Assessment'[Client_Rating__c] = "Survey 3")

RETURN

(SURVEY_3 - SURVEY_1)/SURVEY_1

All works well, but my issue is that some clients do not complete both Survey_1 and Survey_3 -- we have some churn, so what I'm wondering is how I can ensure this formula is only taking into account clients who have had both a 1st and 3rd survey?

The client survey data is contained in a table similar to the below - any guidance/suggestion/tips/tricks are really appreciated!

ClientNum  Survey   Rating

123             1            5

123             3            3

456             1            2

In the above example, I'd really only like to include Client 123 in my calculation, considering they have both a first and third survey -- I'd just move forward with my initial DAX, but I don't want to make the assumption that client 456 will have a third survey at some point and when that happens, the % change would 'become' accurate. So, in the above, the decline would ideally show as -60% (5 to 2) instead of -42% (3.5 to 2 - with the 3.5 being the avg of both clients' scores at Survey 1). Client 456 would eventually be factored into the % change once they had completed Survey 3.

I was thinking a conditional column I could add on as a filter, but I just learned that filters don't work when using VARs in DAX (DAX newbie here 🙂 - so, I'm not sure how to accomplish.

Thanks again for any assistance, PBI Community!

[daxer-almighty]

[Change Rate] =
var ClientsWithBothSurveys =
    FILTER(
        DISTINCT( T[ClientNum] ),
        // Based on the info you've given,
        // the client can have either 1
        // or 2 surveys. You want only
        // the clients with 2 surveys.
        CALCULATE(
            DISTINCTCOUNT( T[Survey] ) = 2
        )
    )
var AvgRating1 =
    CALCULATE(
        AVERAGE( T[Rating] ),
        ClientsWithBothSurveys,
        KEEPFILTERS(
            T[Survey] = 1
        )
    )
var AvgRating3 =
    CALCULATE(
        AVERAGE( T[Rating] ),
        ClientsWithBothSurveys,
        KEEPFILTERS(
            T[Survey] = 3
        )
    )    
var Result =
    DIVIDE(
        AvgRating3 - AvgRating1,
        AvgRating1
    )
return
    Result

However, having a model with just one table is one where you're gonna face issues, even if your formulas are perfectly correct.

Watch this as a warning: https://www.youtube.com/watch?v=aRntX-HiiN8&t=270s