Filters when using firstnonblank measure as vlookup

DAX DAX Calculations
1

I have the following dax, designed to retrieve a value from another table in which key columns are shared.

BookingDate = CALCULATE (
FIRSTNONBLANK(FactSurgery[BookingDate], 1 ),
FILTER ( ALL (factsurgery),
factsurgery[PatientName] = clients[id]
&& FactSurgery[SurgeryStatus] = “Completed”
|| FactSurgery[SurgeryStatus] = “Completed - Prev. Rescheduled”) )

Here’s what I’m trying to accomplish:

Retrieve FactSurgery[BookingDate] in which the following 2 criteria are met:

(1) Factsurgery[PatientName] = dimclients[id]

AND

(2) FactSurgery[SurgeryStatus] = “Completed” OR FactSurgery[SurgeryStatus] = “Completed - Prev. Rescheduled”

I want the firstnonblank to return a match when Factsurgery[PatientName] = clients[id] but only when the surgery status is either “Completed” or “Completed - Prev. Rescheduled”. If the surgery status is anything other than these 2 values, I want the result to be blank.

Is this the right formatting?

2

HI,

It look good to me are you facing some issue with above dax.

Thanks,
Anurag

3

Hi @pete.langlois. Possibly order of operations … try brackets after the && and surrounding the || clause. If this doesn’t help, please provide a PBIX (with sample data if necessary) for the forum members to investigate further.
Greg

4

Hello @pete.langlois, we noticed no response from you since November 11.

Did the responses above help you with your inquiry?

5

Hi @pete.langlois, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.