Forum Discussion

Williams25k's avatar
Williams25k
Frequent Visitor
1 year ago
Solved

LOOKUPVALUE with multiple return values

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 I...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    1 year ago

    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

     

     

     

     

PhilipTreacy's avatar
PhilipTreacy
Super User
1 year ago

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's avatar
Williams25k
Frequent Visitor
1 year ago

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!