Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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. 


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]),
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.

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]))




View solution in original post

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/2027Trillium Medicaid   202188/5/2022
XXXXX-6ALauren12/27/20061512/27/2027NC Healthchoice440208126/27/2022
XXXXX-07Angel9/23/2002199/23/2023UHC Community Plan - Medicaid440202188/16/2022
XXXXX-34Alana2/17/2012102/17/2033Trillium Medicaid   204168/12/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-00Malyah10/4/20021910/4/2023Carolina Complete Health - Medicaid440202185/27/2022
XXXXX-9AJayden5/25/2007155/25/2028Medicaid440   7/5/2022
XXXXX-3ACaelyn8/8/2002198/8/2023Medicaid   201284/21/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/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-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]))




Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.