Dax Calculated Column to Count Number of Patients

[calebmfoster]

Hello,

I have two data tables, one with a distinct list of medical practices and one with a list of patients that belong to that practice along with when they were enrolled or if they've disenrolled in our program. I need to get a count of the number of patients at each practice for billing purposes. It must be done in DAX rather than in Power Query because the date range will change , and therefore, the number of active patients at the time of billing will change. Below is an example of each table and the results I am trying to achieve.

Practice Table:

Practice Name	Pracitce ID
Practice 1 Name	45
Practice 2 Name	29

Patient Table:

Patient Name	Practice ID
Patient A	45
Patient B	45
Patient C	29

Final Practice Table:

Practice Name	Practice ID	Number of Patients
Practice 1 Name	45	2
Practice 2 Name	29	1

[lukiz84]

amit he want's to slice from and to dates, the calculated column won't help here...

[amitchandak]

@calebmfoster , A new column in Practice table

countx(filter(Patient , Patient[Practice ID] = Practice [Practice ID]),Patient[Practice ID])

[lukiz84]

Where is the date from/to?

[calebmfoster]

The enrollment from and to dates are in the patient table. They're being used as a slicer on the report. I should note, that I inherited this report and don't currently have permission to alter the data layout.

[lukiz84]

no need to alter it, i figured it would be in Patient table, makes the most sense here. Do you have a seperate date table in place or do you want to set the slicers on the Date from / To fields?

[calebmfoster]

I'd like to set it on the from to fields