Greetings Power BI Community:
I'm trying to create a Power BI report that will display information about clients who are considered high-utillizers and low-utilizers of our local hospital and emergency room facilities. My data model contains the following tables and columns:
ClientMedicalEpisode - [EpisodeID], [ClientID], [MedicalEpisodeTypeID], [AdmitDate], and [DischargeDate]
- this table tracks each time a client goes into and out of the hospital or emergency room
- MedicalEpisodeTypeID indicates the type of Medical Episode: Hospitalization (Medical), Hospitalization (Mental Health), ER Visit (Medical), ER Visit (Mental Health)
ClientProgramStatus - [ClientProgramStatusID], [ClientID], [ProgramAdmitDate], and [ProgramDischargeDate]
- this table tracks each program through which a client is receiving care from our agency
Client - [ClientID], [FirstName] and [LastName]
- this table is the list of client names
DateTable - [Date], and [MonthNameYear]
- this table is a basic date table
ClientMedicalEpisode is related to ClientProgramStatus using ClientMedicalEpisode[ClientID] and ClientProgramStatus[ClientID]
ClientMedicalEpisode is related to Client using ClientMedicalEpisode[ClientID] and Client[ClientID]
ClientMedicalEpisode is related to DateTable using ClientMedicalEpisode[AdmitDate] and DateTable[Date]
What I would like to be able to do is create measures that do the following:
Measure #1 - number of Hospitalization (Medical) episodes that have occurred in the previous 6 months, including 0
Measure #2 - number of Hospitalization (Mental Health) episodes that have occurred in the previous 6 months, including 0
Measure #3 - number of ER Visits (Medical) episodes that have occurred in the previous 6 months, including 0
Measure #4 - number of ER Visits (Mental Health) episodes that have occurred in the previous 6 months, incuding 0
Measure #5 - number of all medical episodes that have occurred in the previous 6 months
I would use the measures in a matrix with rows [MonthNameYear], [ClientLast] and columns [Measure #1]...[Measure #5] so I can show the number of episode types for each client for each month and year.
In a second matrix, I would like to show--for each month and year--just the client(s) who had the highest number of episodes in Measure #5
These measures and visualizations will help to identify the number of, names of, and degree of utilization of our high-utilizers.
In a third matrix, I would like to show rows [ClientLast] and columns [Measure #1]...[Measure #5] for clients whose [ProgramAdmitDate] is more than or equal to 365 days before today AND whose number of all medical episodes in the prevous 6 months is less than or equal to 1 (e.g., [Measure #5] <= 1)
In a card, I would like to show the number of clients whose [ProgramAdmitDate] is more than or equal to 365 days before today AND whose number of all medical episodes in the prevous 6 months is less than or equal to 1 (e.g., [Measure #5] <= 1)
These visualizations will help to identify the number of and names of our low-utillizers. Unfortunately, I have no clue about how to go about creating the low-utillizer visualizations.
Any assistance, insights, etc. anyone can offer in creating the measures, visualizations, or other aspects of the above report would be welcomed and greatly appreciated.
Regards,
Rich
