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
anwilkins
Resolver II
Resolver II

Calculate(Sum) Measure creates multiple rows based on unrelated variables

In short, 1 units equals 15 min of a doctors time.

In this program, a patient is allowed 4 units for an assessment & 20 units for therapy.

The request is to have a weekly report showing how many units each patient has so there is time to get a reauthorization request submitted before the last appointment date. 

My measures are counting fine however, when a patient switches insurance plans or has a birthday during the date range, 

the chart creates 2 lines and I only want 1 line per patient. 

anwilkins_0-1660918031324.png

Chart Explanation

In patient 'Jane', I have 12 units applied to Medicaid and 2 units applied to Trillium; the true remaining therapy units should be 6.

In patient 'Bullocken' the true remaining should be 14

*** Note, when the Credited insurance Carrier column is removed from the chart, the CALCULATE(SUM) function works fine but that column must be displayed. 

I need help adding some dax to my measure(s) that counts all units but filters the insurance table to only display the last insurance used, BUT.....I don't know how to do that. 

The base column that sets the unit paremiters says if medical cpt code 97802 is used, it gets 4 and if 97803 is used, it gets 20. The measures are built off this new column. 

Diatician Units =
If(MERGE_BillingSUMMARY[CPT Code] = "97802", "4",
If(MERGE_BillingSUMMARY[CPT Code] = "97803", "20"))
 
The three measures are:
Allowed Therapy = CALCULATE(MAX(MERGE_BillingSUMMARY[*C-Diatician Units]),FILTER(MERGE_BillingSUMMARY, MERGE_BillingSUMMARY[*C-Diatician Units] = "20"))
 
Applied Therapy = CALCULATE(SUM(MERGE_BillingSUMMARY[Units]),
FILTER(MERGE_BillingSUMMARY,MERGE_BillingSUMMARY[CPT Code] IN {"97803"}))
 
Remaining Therapy = MERGE_BillingSUMMARY[**Allowed Therapy] - MERGE_BillingSUMMARY[**Applied Therapy]
 
Please tell me on what measure to add the dax that says count all units regardless of who the insurance carrier was at the time but only display the last insurance carrier.
 
Thanks and no...I cannot submit sample data for this one.
 
 
1 ACCEPTED SOLUTION

Remaining = Average (Allowed) - Sum (Applied)   (while removing the Insurance and date filter)

 

Remaining Follow-up = CALCULATE(AVERAGE('Table'[Allowed Follow-up])-sum('Table'[Applied Follow-up]),REMOVEFILTERS('Table'[Service Date],'Table'[Insurance]))

lbendlin_0-1661204880925.png

 

 

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

You can use REMOVEFILTERS in this scenario, specifically REMOVEFILTERS(...,[Credited Carrier])

Thanks, any chance you can be a bit more specific? As in...which of the measures do I need to incorporate the "REMOVEFILTERS' into and what would the entire statement then look like?

no sample data = no further help possible.

Unfortunately company policy does not allow me to link to this site. I have included data that you can copy and paste into excel or email me directly and I will provide the file. As a reminder, the end goal is to have a single line that shows the last insurance provider listed on the latest date of service and the remaining units regardless of which insurance paid for a given visit.

Thanks again for any assistance anyone can offer.

Client ID Patient DOB Visit Age 21st BD Insurance Allowed Assess Applied Assess Remaining Assess Allowed Follow-up Applied Follow-up Remaining Follow-up Latest Service

Client IDPatientDOBVisit Age21st BDInsuranceAllowed AssessApplied AssessRemaining AssessAllowed Follow-upApplied Follow-upRemaining Follow-upLatest Service
XXXXX-76Jane12/8/20061512/8/2027Medicaid440201287/14/2022
XXXXX-76Jane12/8/20061512/8/2027Trillium Medicaid   202188/5/2022
XXXXX-47Juanita4/21/2005174/21/2026Medicaid440202185/3/2022
XXXXX-6ALauren12/27/20061512/27/2027NC Healthchoice440208126/27/2022
XXXXX-42Alyssa1/18/2004181/18/2025Medicaid440204166/27/2022
XXXXX-07Angel9/23/2002199/23/2023UHC Community Plan - Medicaid440202188/16/2022
XXXXX-41Kirsten8/4/2002198/4/2023Medicaid440202187/14/2022
XXXXX-34Alana2/17/2012102/17/2033Medicaid440204167/22/2022
XXXXX-34Alana2/17/2012102/17/2033Trillium Medicaid   204168/12/2022
XXXXX-5AAlex10/2/20061510/2/2027Medicaid440202184/1/2022
XXXXX-61Emma 5/27/2004175/27/2025Healthy Blue - Medicaid440204163/28/2022
XXXXX-04Adrienne3/1/2002193/1/2023UHC Community Plan - Medicaid   202181/7/2022
XXXXX-96Ella7/31/2005167/31/2026BCBS NC   204162/7/2022
XXXXX-85John3/6/2011103/6/2032Medicaid440   2/28/2022
XXXXX-85John3/6/2011113/6/2032Medicaid   202183/24/2022
XXXXX-21LAYTON10/3/20101110/3/2031AmeriHealth - Medicaid440203177/22/2022
XXXXX-3ACarmen 12/24/20031812/24/2024Healthy Blue - NCHC Medicaid440202184/25/2022
XXXXX-66Joslyn9/18/2003189/18/2024NC Healthchoice440202184/8/2022
XXXXX-15Trey 6/30/2007146/30/2028Medicaid   204166/16/2022
XXXXX-15Trey 6/30/2007146/30/2028WellCare - Medicaid440   4/4/2022
XXXXX-15Trey 6/30/2007156/30/2028Trillium Medicaid   202188/8/2022
XXXXX-50Ashley12/24/20041712/24/2025WellCare - Medicaid   201826/3/2022
XXXXX-71JACOB10/7/20041710/7/2025BCBS NC   202181/4/2022
XXXXX-86Isabelle10/28/20051610/28/2026Medicaid422208128/1/2022
XXXXX-56Camila6/4/2006156/4/2027Medicaid440202183/29/2022
XXXXX-00Malyah10/4/20021910/4/2023Carolina Complete Health - Medicaid440202185/27/2022
XXXXX-9AJayden5/25/2007155/25/2028Medicaid440   7/5/2022
XXXXX-4AJariel10/3/20051610/3/2026Medicaid4402010106/30/2022
XXXXX-3ACaelyn8/8/2002198/8/2023Medicaid   201284/21/2022
XXXXX-37Jeremiah7/12/201297/12/2033Medicaid422202185/31/2022
XXXXX-25Ryan7/3/2006157/3/2027Healthy Blue - Medicaid440204166/28/2022
XXXXX-25Ryan7/3/2006167/3/2027Healthy Blue - Medicaid   206148/16/2022
XXXXX-85KELSEY10/26/20041710/26/2025Medicaid   202182/8/2022
XXXXX-78Karizma10/18/20021910/18/2023Healthy Blue - Medicaid440   2/10/2022
XXXXX-25Lola7/26/2004177/26/2025WellCare - Medicaid440   4/26/2022
XXXXX-86Modesty6/2/2005176/2/2026Medicaid440   6/9/2022
XXXXX-46MELISA2/24/2007152/24/2028Carolina Complete Health - Medicaid440202186/24/2022
XXXXX-5ANaudia12/22/20051612/22/2026Medicaid440206147/21/2022
XXXXX-5ANaudia12/22/20051612/22/2026Trillium Medicaid   202188/9/2022
XXXXX-44Jaidyn8/1/2009128/1/2030AmeriHealth - Medicaid440202184/7/2022
XXXXX-55Delilah Blue12/29/20071412/29/2028Beacon Health Options440202184/4/2022
XXXXX-51Isabella7/11/2005167/11/2026NC Healthchoice4402024-47/5/2022
XXXXX-51Isabella7/11/2005177/11/2026NC Healthchoice422208128/15/2022
XXXXX-7AJosephine9/6/2004179/6/2025Medicaid   202181/6/2022
XXXXX-9APAMELA3/12/2003183/12/2024Medicaid422204162/18/2022
XXXXX-69Jacob4/3/2009124/3/2030BCBS NC   202183/1/2022
XXXXX-69Jacob4/3/2009124/3/2030WellCare - Medicaid440207133/29/2022
XXXXX-74Hannah8/27/2010118/27/2031Medicaid440   1/28/2022

 

Remaining = Average (Allowed) - Sum (Applied)   (while removing the Insurance and date filter)

 

Remaining Follow-up = CALCULATE(AVERAGE('Table'[Allowed Follow-up])-sum('Table'[Applied Follow-up]),REMOVEFILTERS('Table'[Service Date],'Table'[Insurance]))

lbendlin_0-1661204880925.png

 

 

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.