Sorting order with Vlookup (firstnonblank)

DAX DAX Calculations
1

I’m trying to replicate the excel vlookup function in Power BI, and I have a question about how to return values based on a specific filter order.

I’m trying to fetch surgery ID’s from a surgery table and map them onto the touchpoints table. The relationship is many-to-one. That is, there is only 1 Lead ID on the touchpoints table, however each Lead could show up in multiple records on the surgery table. I understand that firstnonblank fetches the first match it finds, however, I’m wondering if I can use DAX to fetch the surgery ID based on a specific sorting order for surgery status.

I would like to return the surgery ID in the following order:

surgery status = blank. If there’s no matches for a blank surgery status, then
surgery status = completed
then surgery status = cancelled
then surgery status = late cancelled
and finally, surgery status = Patient DNS.

My current DAX is fetching values as expected, but I’m not sure it’s finding matches in the correct order.

Surgery ID =

CALCULATE (FIRSTNONBLANK(FactSurgeryDetails[SurgeryID], 1 ),
FILTER ( ALL (factsurgerydetails), FactSurgeryDetails[Lead ID] = facttouchpoints[LeadID] ),
    FactSurgerydetails[SurgeryStatus] = BLANK()
    || FactSurgerydetails[SurgeryStatus] = "Completed"
    || FactSurgerydetails[SurgeryStatus] = "Cancelled"
    || FactSurgerydetails[SurgeryStatus] = "Late Cancelled"
    || FactSurgerydetails[SurgeryStatus] = "Patient DNS"),

)

Is this the correct way to structure the dax to instruct the firstnonblank to return values in the order specified above? I was thinking that I may have to separate each of the surgery status filters using commas:

Surgery ID =

CALCULATE (FIRSTNONBLANK(FactSurgeryDetails[SurgeryID], 1 ),
FILTER ( ALL (factsurgerydetails), FactSurgeryDetails[Lead ID] = facttouchpoints[LeadID] ),
    FactSurgerydetails[SurgeryStatus] = BLANK(),
    FactSurgerydetails[SurgeryStatus] = "Completed",
    FactSurgerydetails[SurgeryStatus] = "Cancelled",
    FactSurgerydetails[SurgeryStatus] = "Late Cancelled",
    FactSurgerydetails[SurgeryStatus] = "Patient DNS")

)

@BrianJ , any ideas?

Thanks!
Pete

2

Bumping this post for more visibility.

3

Hi @pete.langlois - FIRSTNONBLANK doesn’t works the way it is used in the example. It will return the first occurrence of Surgery ID available in Filtered table irrespective of the condition provided.

You may look to split the required conditions into different calculations and then write a Switch statement to select the required one.

Request to provide a sample PBIX file if further help is required.

Thanks
Ankit J

4

Hello @pete.langlois, it’s been a while since we got a response from you.

Just following up if you still need help with your inquiry?

If you do, kindly provide the information @ankit requested above so they can help you further.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Due to inactivity, we’ll be marking this post as solved.