Filter for a Value Based on a Date (which is being filtered for based on some conditions)

[samdep]

Hi All,

The expression below groups by client number, then looks for the earliest date at which the client was identified as a VIP client based on a survey they took - which flagged them as a VIP client (conditional column based on a threshold score). All appears to be working well there.

What I'd like to do though is also pull in the name of the survey that that were flagged at in another column - as the client can take multiple, but I'm stuck.

Master VIP Client List (Earliest Date of ID) =

VAR FIRST_DATE = CALCULATE(FIRSTNONBLANK('Survey'[CreatedDate],1),FILTER(ALLEXCEPT('Survey',' Survey'[Client.ClientNumber]),'Survey'[VIP Client Y/N]="VIP"))

RETURN

IF('Survey'[VIP Client Y/N]="VIP",FIRST_DATE)

Basically, my table looks like the below... The last column (Survey Name) is what I am looking to pull in.

ClientID   Survey Name  Survey Score  Survey Date  Is VIP Client       Earliest Date Flagged Survey Name    Survey Name

1234         Survey 1          12                    1/1/21         Yes                    1/1/21                                                 (Should Be) Survey 1

1234         Survey 2          12                    1/2/21         Yes                    1/1/21                                                 Survey 1

5678        Survey 1           8                    1/1/21           No                     1/2/21                                                 Survey 2

5678         Survey 2          12                    1/2/21         Yes                     1/2/21                                                 Survey 2

Off of the above, the client was flagged as a VIP on two surveys -- the expression I pasted above provides in a new conditional column the first/earliest date at which they were flagged... and now, I'm just looking to pull in the Survey Name associated with that date.

Thank you in advance for any help - very much appreciated!

[lbendlin · Accepted solution]

You've already got your client ID , and your earliest survey date.  Do another Calculate to find the "MAX" (or whatever)  survey name for these two filters.

[samdep]

Makes complete sense, I had just been struggling with how to lay it out - but was able to use an expression in a measure similar to the below, shared by Amit in another thread - just in case anyone ever stumbles across this post and has a similar need:

Measure =
VAR __id = MAX ('Table'[ClientID] )
VAR __date = CALCULATE ( MAX('Table'[PurchaseDate] ), ALLSELECTED ('Table' ), 'Table'[ClientID] = __id )
CALCULATE ( max ('Table'[ItemPurchased] ), VALUES ('Table'[ClientID] ),'Table'[ClientID] = __id,'Table'[PurchaseDate] = __date )