New vs Existing Patients

Question

Hi All,&nbsp;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:&nbsp;Service Date&nbsp; &nbsp; &nbsp; PatientID&nbsp; PhysicianID&nbsp; &nbsp;2016-10-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22016-12-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;452017-10-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22018-10-11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;32018-12-05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp;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:&nbsp;Patient Orders=SUMMARIZE (Fact_Rx,Fact_Rx[PatientID],"First Order",MIN(Fact_Rx[ServiceDate]),"Last Order", MAX (Fact_Rx[ServiceDate]))&nbsp;New Patients=CALCULATE(countdistinct(PatientID),FILTER(ALL('Patient Orders'),'Patient Orders'[First Order]&gt;=DATE(2017,10,01))&nbsp;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.&nbsp;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)&nbsp; .Any help is appreciated&nbsp;&nbsp;

mattallington · Answer

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

ashish_mathur · Answer

Hi,
You may download my PBI file from here.
Hope this helps.

anonymous · Answer

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

fuhrer786 · Answer

MattAllington&nbsp; 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.&nbsp;

fuhrer786 · Answer

Ashish_Mathur&nbsp; 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

Forum Discussion

New vs Existing Patients

6 Replies

Related content

How to copy existing semantic model ?

Query existing semantic model

Remove existing DataSource

Average Daily Patient Visits Calculation

Patient Tracker and Package Tracker