Help with my first DAX formula relating two tables

[Anonymous]

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!

[v-ljerr-msft]

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

[Anonymous]

Are there are any speed or efficiency considerations when deciding to use the FILTER function?

Thanks in advance

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

[BhaveshPatel]

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.