Earliest Appt in the Future for each Patient

[Anonymous]

Good afternoon, I have 2 tables. Does anyone have suggestions for the following DAX query?

1. Patient - One row per unique pt identifier called PAT_ID

2. Encounters - One row for each patient's visit, foreign key is the pt unique identifier

In the patient column, I would like to add a field on the patient table that gives each patient's (see image)

        a. first appt in the future

        b. with an appt status name = 'Scheduled'

[Anonymous · Accepted solution]

-- this DAX goes into a calculated column
-- in the Patient table

var __patientId = Patient[PAT_ID]
var __now = TODAY()
var __firstScheduledApptDate =
	CALCULATE(
		MIN( Encounters[APPT_DATE_TM] ),
		Encounters[APPT_STATUS] = "Scheduled",
		Encounters[PAT_ID] = __patientId,
		Encounters[APPT_DATE_TM] > __now
	)
return
	__firstScheduledApptDate

[Anonymous]

I appreciate your response. Do you have any suggestions for the error message below?

[Anonymous]

1. Are you creating this as a CALCULATED COLUMN? This is NOT A MEASURE.

2. Secondly, you have to give the column a name. As you can see in the error message, you're trying to name the column "var __patientId." Please name your column:

[Column Name] = <your formula here>

Best

Darek

[Anonymous]

You are absolutely correct

FirstSchApptDate = var __patientId = 'Patients'[PAT_ID]
var __now = TODAY()
var __firstScheduledApptDate =
CALCULATE(
MIN( 'Encounters'[APPT_DATE_TM] ),
'Encounters'[APPT_STATUS] = "Scheduled",
'Encounters'[PAT_ID] = __patientId,
'Encounters'[APPT_DATE_TM] > __now
)
return
__firstScheduledApptDate