Related MAX

[jcawley]

Hello all,

I am looking to see the last provider a customer has seen in a calculated column.

Specifically, what provider was last seen by a customer with a completed appointment. I have two tables. A customer table, that has unique customer IDs, and an appointments table which has a listing of all appointments and their assosciated details.

 Customer Table

Customer ID	Last Provider seen?
111
111
111
222
222
333

Appointment Table

Customer ID	Provider	Appointment Date	Status
111	J. Doe	9/5/2020	Completed
111	J. Doe	10/5/2020	Completed
111	A. Doe	11/5/2020	Completed
222	B. Doe	8/5/2020	Completed
222	J. Doe	7/5/2020	Not Confirmed
333	C. Doe	11/5/2020	Completed

So far I have been trying to use LOOKUPVALUE, but to no avail. It seems to be pulling data, just not specific to Customer ID.

Last Provider Seen =

LOOKUPVALUE(

Appointments[Provider],

Appointments[Appt Date Time],maxx(filter(Appointments, Appointments[Status]="Completed"),Appointments[Appt Date Time])

)

Here is what the desired output would be in the Customer table:

Customer ID	Last Provider seen?
111	A. Doe
222	B. Doe
333	C. Doe

Any help would be greatly appreciated!

[v-rzhou-msft · Accepted solution]

Hi @jcawley 

I build a sample like yours to have a test.

My Customer Table is a calculated table and I think you may build a relationship between Customer ID columns in two tables.

Customer = VALUES(Appointment[Customer ID])

Then I add a calculated column in Customer Table.

Last Provider Seen = 
VAR _LastDate =
    MAXX (
        FILTER (
            Appointment,
            Appointment[Status] = "Completed"
                && Appointment[Customer ID] = EARLIER ( Customer[Customer ID] )
        ),
        Appointment[Appointment Date]
    )
RETURN
    CALCULATE (
        MAX ( Appointment[Provider] ),
        FILTER (
            Appointment,
            Appointment[Customer ID] = EARLIER ( Customer[Customer ID] )
                && Appointment[Status] = "Completed"
                && Appointment[Appointment Date] = _LastDate
        )
    )

Result is as below.

You can download the pbix file from this link: Related MAX

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[jcawley]

Thank you, RicoZhou! That is the ticket!

Much appreciated.

[amitchandak]

Try a new column in customer table like

maxx(filter(Appointments, Appointments[Status]="Completed" && Appointments[Customer ID] = Customer[Customer ID]), lastnonblankvalue(Appointments[Appt Date Time], Appointments[Provider]))

[jcawley]

Hmm, not quite there! It's certainly doing something, but its not always pulling the max date provider.

[AilleryO]

Hi,

Did you make a link between your table ?

https://docs.microsoft.com/en-US/power-bi/transform-model/desktop-create-and-manage-relationships

Have you been trying a RELATED()?

[jcawley]

Hello AilleryO,

I did, I'm just not sure how to plug RELATED into what I am looking for. I've only used it for simple things so far.