cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reine
Helper III
Helper III

How to get the number of days a provider worked based on number of patients seen

Hi all - I'm having some difficulty figuring this one out.

 

I need to calculate how many patients a doctor see's per day which is easy enough in a matrix visual.  But with that data, I then need to find out how many days a doctor worked each month based on the number of patients they saw in a day.   And, to make things more difficult, that is different for each doctor.

For Dr 1, we only count a day as worked if the doctor saw 3 or more patients.  For Dr 2 and Dr 3, we only count a day as worked if they saw 2 or more patients.

Here is a table with sample data.  I have a seperate date table that is linked on Appointment Date:

AppointmentDateAppointmentResourcePatient Name
10/02/20Dr 1Pt 26
10/02/20Dr 2Pt 43
10/02/20Dr 2Pt 44
10/02/20Dr 3Pt 25
10/02/20Dr 3Pt 37
10/02/20Dr 1Pt 38
10/02/20Dr 1Pt 30
10/09/20Dr 3Pt 48
10/10/20Dr 2Pt 39
10/10/20Dr 1Pt 40
10/10/20Dr 3Pt 46
10/10/20Dr 1Pt 35
10/10/20Dr 1Pt 36
10/10/20Dr 3Pt 47
10/13/20Dr 3Pt 33
10/13/20Dr 3Pt 49
10/16/20Dr 1Pt 27
10/23/20Dr 2Pt 31
10/23/20Dr 2Pt 45
10/23/20Dr 2Pt 34
10/23/20Dr 2Pt 50
10/23/20Dr 1Pt 29
10/23/20Dr 1Pt 32
10/23/20Dr 2Pt 41
10/23/20Dr 3Pt 42
10/23/20Dr 3Pt 28
11/02/20Dr 1Pt 12
11/02/20Dr 2Pt 18
11/02/20Dr 1Pt 16
11/02/20Dr 3Pt 23
11/02/20Dr 2Pt 6
11/02/20Dr 2Pt 9
11/02/20Dr 1Pt 22
11/05/20Dr 3Pt 9
11/10/20Dr 1Pt 22
11/10/20Dr 2Pt 14
11/10/20Dr 2Pt 1
11/10/20Dr 1Pt 7
11/10/20Dr 3Pt 21
11/10/20Dr 2Pt 24
11/11/20Dr 2Pt 11
11/11/20Dr 3Pt 2
11/11/20Dr 3Pt 4
11/11/20Dr 3Pt 20
11/11/20Dr 1Pt 15
11/11/20Dr 2Pt 5
11/17/20Dr 2Pt 11
11/17/20Dr 2Pt 13
11/17/20Dr 1Pt 3
11/17/20Dr 1Pt 17
11/17/20Dr 2Pt 14
11/17/20Dr 3Pt 10
11/17/20Dr 1Pt 19
11/17/20Dr 2Pt 8

 

The results I am looking for are:
  
Pts seen per day per Dr.PNGDays worked per month by Dr.PNG




 

 

 

 

 

 

 

 

I have a measure that calculates the days worked, but can't figure out how to make it filter out days that have less than 3 appointments for Dr 1, and less than 2 appointments for Dr 2 and Dr 3:


Days Worked sample data = SUMX ( Values('data test'[AppointmentResource]), Calculate(DistinctCount('data test'[AppointmentDate])) )

Thank you for your help 🙂
1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @Reine ,

 

Would you please try to use the following measure :

 

DAYSWORKED =
VAR a =
    SUMMARIZE (
        'Table',
        'Table'[AppointmentResource],
        'Table'[AppointmentDate],
        "num", COUNT ( 'Table'[Patient Name] )
    )
RETURN
    COUNTX (
        FILTER (
            a,
            IF ( 'Table'[AppointmentResource] = "Dr 1", [num] >= 3, [num] >= 2 )
        ),
        'Table'[AppointmentDate]
    )

 

Capture1.PNG

 

For more details, please refer to the sample pbix :https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXjEkwY5GtxPn-kgsZ...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

4 REPLIES 4
Reine
Helper III
Helper III

@v-deddai1-msft thank you so much! This works perfectly.

Both replies are so helpful to my learning and understanding of PBI and DAX. I greatly appreciate your time!

May the new year bring you much joy 🙂

v-deddai1-msft
Community Support
Community Support

Hi @Reine ,

 

Would you please try to use the following measure :

 

DAYSWORKED =
VAR a =
    SUMMARIZE (
        'Table',
        'Table'[AppointmentResource],
        'Table'[AppointmentDate],
        "num", COUNT ( 'Table'[Patient Name] )
    )
RETURN
    COUNTX (
        FILTER (
            a,
            IF ( 'Table'[AppointmentResource] = "Dr 1", [num] >= 3, [num] >= 2 )
        ),
        'Table'[AppointmentDate]
    )

 

Capture1.PNG

 

For more details, please refer to the sample pbix :https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXjEkwY5GtxPn-kgsZ...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

Greg_Deckler
Super User IV
Super User IV

@Reine Well, first I would create a table with the doctor's ID and the number of patients per day that constitutes a "work day". You should then be able to more or less replicate your matrix display as a table by using GENERATE to create a Cartesian product (as a table VAR) of your doctors and your dates. Use ADDCOLUMNS to add a column for the number of patients seen by that doctor on that day. Then you can use LOOKUPVALUE to grab the number of patients in a day that count as a work day. FILTER your table by that number <= and COUNTROWS.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler 
Thank you Greg.  I'm afraid my PBI and DAX knowledge is pretty basic so I only understand part of this 🙂  I created the separate table "Doctor Count as Day" with 2 columns: doctor's ID and the number of patients per day that constitutes a "work day".  If I understand correctly, I should now create a new table using GENERATE to combine my Date table and the new "Doctor Count as Day" table, is that right? 


Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors