topic Re: Earliest Appt in the Future for each Patient in DAX Commands and Tips

Earliest Appt in the Future for each Patient

Anonymous — Wed, 26 Jun 2019 20:53:35 GMT

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'

Re: Earliest Appt in the Future for each Patient

Anonymous — Thu, 27 Jun 2019 09:47:20 GMT

-- 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

Re: Earliest Appt in the Future for each Patient

Anonymous — Thu, 27 Jun 2019 13:07:08 GMT

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

Re: Earliest Appt in the Future for each Patient

Anonymous — Thu, 27 Jun 2019 13:20:28 GMT

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

Re: Earliest Appt in the Future for each Patient

Anonymous — Thu, 27 Jun 2019 13:51:08 GMT

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