Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
amit he want's to slice from and to dates, the calculated column won't help here...
@calebmfoster , A new column in Practice table
countx(filter(Patient , Patient[Practice ID] = Practice [Practice ID]),Patient[Practice ID])
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |