Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |