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
Fuhrer786
Frequent Visitor

New vs Existing Patients

Hi All,

 

I have patients medical claims data for 2 years FY 2017/2018 (FY runs from October 1st to Sept 30th). I have couple of tables like Fact_RX, DimProvider, DimDate,etc..The sample fact data looks like:

 

Service Date      PatientID  PhysicianID   

2016-10-01             1              2

2016-12-01             2             45

2017-10-01             3             2

2018-10-11             1             3

2018-12-05             1             2

 

What I am trying to show in report is how many patients are New Starts in FY 2018(20171001 to 20180930 timeframe). I have this so far:

 

Patient Orders=

SUMMARIZE (
Fact_Rx,
Fact_Rx[PatientID],"First Order",MIN(Fact_Rx[ServiceDate]),
"Last Order", MAX (Fact_Rx[ServiceDate])
)
 
New Patients=
CALCULATE(countdistinct(PatientID),FILTER(ALL('Patient Orders'),'Patient Orders'[First Order]>=DATE(2017,10,01))
 
While, the above measure gives me correct count in general for 2018,but when I filter on any provider ID it misses some patients. For e.g.
PatientID 1 is not new patient for FY 2018 in general but for provider ID 3 sepecifically he/she is a new patient which the above measures misses. The problem arises when patient changes providers like above example.
 
I want a KPI that should new patients in general for FY 2018 but it should work properly when filtered by any dimesion like ProviderID,HospitalName(present in DImprovider)  .Any help is appreciated

 

 

6 REPLIES 6
Anonymous
Not applicable

You must use ALLEXCEPT instead of ALL, just add ProviderID in ALLEXCEPT DAX expressions and on this way you could filter ProviderID.
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Thanks for taking the effort to help me. However, When I don't have any filters on physician_ID its shows all patients are new Patients overall which is incorrect as Only PatientID 3 is new patient

Hi,

My solution return the number of new patients every month.  So if no selection is made in the PhysicianID slicer, then you see new patients in each month for all Physician ID's


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 This is quite a complex topic with a relatively simple solution. In short you need to set up a star schema datamodel.  You should create a patient dimension table and also a Calendar dimension table.   When you use the ALL function for the patient table, it should do what you need.   Note: I haven’t tested this - this is just my assumption of what the issue is.  



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@MattAllington  I have all dimensions setup for Dates, Providers, etc... When I add New Patients on Tooltip anywhere in report, it shows me correct count but when I click on specific provider or any dimension it misses some patients. 

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.