LOOKUPVALUE with multiple return values

[Williams25k]

I have two different tables. One serves as a master list of all ID numbers for a group of people. 

The other table has appointment information of those ID numbers. In the appointment table, the ID numbers exist in multiple rows as they have had multiple appointments over time as well as future appointments. 

I need to create a column on the master list that shows their last completed appointment and their next scheduled appointment. 

I have tried to use LOOKUPVALUE (Appointment Date (on appointment table), ID number (on appointment table), ID number (on master table) but get the message: "A table of multiple values was supplied where a single value was expected"

I need to be able to have it populate the last date completed (filtered by appointment status of "Comp") and the next appointment scheduled (filtered by appointment status of "Sched").

So I am looking to solve the min/max or first/last problem in addition to filtering the result by the appointment status.

I could be completely wrong with the formula, but this was my first stab at this:

Any help is greatly appreciated!

[PhilipTreacy · Accepted solution]

Hi @Williams25k 

Download PBIX file with example below

Does your Type column look something like this (the actual text in the Type column has no bearing on the measures) :

If so you can write 2 measures

Last Completed Type = CALCULATE(MAX('NY Appt Master'[Type]), FILTER(('NY Appt Master'), 'NY Appt Master'[ID] = SELECTEDVALUE('NY Appt Master'[ID]) && 'NY Appt Master'[Start Date] = [Last Completed Appt]))

Next Appt Type = CALCULATE(MAX('NY Appt Master'[Type]), FILTER(ALL('NY Appt Master'), 'NY Appt Master'[ID] = SELECTEDVALUE('NY Appt Master'[ID]) && 'NY Appt Master'[Start Date] = [Next Appt]))

and display them in a table

For the 2nd part, with the sample data I have, there are 4 people with previous appointments and 4 people with scheduled appointments, but there are 5 people.  So your measure will give you 100% (4/4) but is this what you really want to?  Are you actually interested in measuring Prev/Scheduled appointments against the total number of people?

Anyway, this will give you what you actually asked for above, if it's not what you are after, let me know

Perc People With Prev & Next Appt = DIVIDE(CALCULATE(DISTINCTCOUNT('NY Appt Master'[ID]), FILTER('NY Appt Master', 'NY Appt Master'[Status] = "Comp")), CALCULATE(DISTINCTCOUNT('NY Appt Master'[ID]), FILTER('NY Appt Master', 'NY Appt Master'[Status] = "Scheduled")))

Regards

Phil

[Williams25k]

Hi Phil, after some tinkering I was able to get this to work exactly as desired. Your help has been tremendous and I could not have asked for better support. Thank you very much!

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[PhilipTreacy]

Hi @Williams25k 

Download PBIX file with example shown below

You should have a relationship between the ID table and the Appointments table.  I've also created a Date Table in my model

I created this sample data

Then wrote these measures for the Last Completed Appt and the Next Scheduled Appt

Last Completed Appt = CALCULATE(MAX('NY Appt Master'[Start Date]), FILTER(ALL('NY Appt Master'), 'NY Appt Master'[ID] = SELECTEDVALUE('NY Appt Master'[ID]) && 'NY Appt Master'[Status] = "Comp"))

Next Appt = CALCULATE(MIN('NY Appt Master'[Start Date]), FILTER(ALL('NY Appt Master'), 'NY Appt Master'[ID] = SELECTEDVALUE('NY Appt Master'[ID]) && 'NY Appt Master'[Status] = "Scheduled"))

Using a table visual you can then see this

Regards

Phil

[Williams25k]

Hi Phil, 

This works beautifully, thank you very much for the help. I have a few additional questions:

1. If there is an associated 'Appointment Type' that lives in the NY Appt Master table and I want to display this with the new measures, how can I write the DAX to display the type of appointment that was completed and the type of appointment that is scheduled? I would need two seperate measures so I can display both.

2. I want a calculated measure that allows me to display the percentage of people who had a previous appointment and have a next appointment - basically sum of people with future appointments divided by sum of people who have completed an appointment (not every ID has a future appointment scheduled. Ideally this woul be displayed on a card. 

Can you please help with these two quetions?

Thank you!