Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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'
Solved! Go to 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
-- 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
I appreciate your response. Do you have any suggestions for the error message below?
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |