Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
calebmfoster
Regular Visitor

Dax Calculated Column to Count Number of Patients

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 NamePracitce ID
Practice 1 Name45
Practice 2 Name29

 

Patient Table:

Patient NamePractice ID
Patient A 45
Patient B 45
Patient C 29

 

Final Practice Table:

Practice NamePractice IDNumber of Patients
Practice 1 Name 45 2
Practice 2 Name 29 1
6 REPLIES 6
lukiz84
Memorable Member
Memorable Member

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

amitchandak
Super User
Super User

@calebmfoster , A new column in Practice table

 

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

lukiz84
Memorable Member
Memorable Member

Where is the date from/to?

 

 

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.

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?

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.