Forum Discussion
Help with my first DAX formula relating two tables
I have two tables (see screenshot below)
1. Encounters - List all appt encounters including the date of the encounter and flags indicating if the encounter is a New Patient, Seen in 10 Business Days, etc.
2. Dates - list of dates including flags to show whether or not the date occurs this month.
I would like to count encounters where New_Patient_Visit = 1 and where Date of Encounter is Equal to a Calendar_DT in the Dates table where Current Month Flag = 1
In SQL it would look like this
SELECT COUNT(E.NEW_PATIENT_VISIT)
FROM ENCOUNTERS E
INNER JOIN DATES D ON E.ENC_DATE = D.CALENDAR_DT
WHERE E.NEW_PATIENT_VISIT = 1 AND D.[Current Month Flag] = 1
Thanks in advance!
4 Replies
- BhaveshPatel
Super User
This measure will count all the patients visit where new patient visit is 1 and current month flag is 1.
Countingpatients:= CALCULATE(COUNTA(Encounters[New_Patient_Visit]),[New_Patient_Visit]="1",[Current Month Flag]="1")
Hoping that this is what you would like to achieve.
v-ljerr-msftMicrosoft Employee
Hi Anonymous,
In addition, using FILTER Function (DAX) should also meet your requirement.
Countingpatients := CALCULATE ( COUNTA ( Encounters[New_Patient_Visit] ), FILTER ( Encounters, Encounters[New_Patient_Visit] = "1" && RELATED ( Dates[Current Month Flag] ) = "1" ) )Regards
- BhaveshPatel
Passing filters direct to CALCULATE is more faster than using iterrator function like FILTER. However, Whenever, you would like to compare columns to columns or columns to measures etc., FILTER function must be used. For simple filters like your report, You can pass direct filters to CALCULATE.
