cancel
Showing results for
Did you mean:
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]),
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
Super User

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

5 REPLIES 5
Super User

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

Resolver II

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?

Super User

no sample data = no further help possible.

Resolver II

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 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 XXXXX-76 Jane 12/8/2006 15 12/8/2027 Medicaid 4 4 0 20 12 8 7/14/2022 XXXXX-76 Jane 12/8/2006 15 12/8/2027 Trillium Medicaid 20 2 18 8/5/2022 XXXXX-47 Juanita 4/21/2005 17 4/21/2026 Medicaid 4 4 0 20 2 18 5/3/2022 XXXXX-6A Lauren 12/27/2006 15 12/27/2027 NC Healthchoice 4 4 0 20 8 12 6/27/2022 XXXXX-42 Alyssa 1/18/2004 18 1/18/2025 Medicaid 4 4 0 20 4 16 6/27/2022 XXXXX-07 Angel 9/23/2002 19 9/23/2023 UHC Community Plan - Medicaid 4 4 0 20 2 18 8/16/2022 XXXXX-41 Kirsten 8/4/2002 19 8/4/2023 Medicaid 4 4 0 20 2 18 7/14/2022 XXXXX-34 Alana 2/17/2012 10 2/17/2033 Medicaid 4 4 0 20 4 16 7/22/2022 XXXXX-34 Alana 2/17/2012 10 2/17/2033 Trillium Medicaid 20 4 16 8/12/2022 XXXXX-5A Alex 10/2/2006 15 10/2/2027 Medicaid 4 4 0 20 2 18 4/1/2022 XXXXX-61 Emma 5/27/2004 17 5/27/2025 Healthy Blue - Medicaid 4 4 0 20 4 16 3/28/2022 XXXXX-04 Adrienne 3/1/2002 19 3/1/2023 UHC Community Plan - Medicaid 20 2 18 1/7/2022 XXXXX-96 Ella 7/31/2005 16 7/31/2026 BCBS NC 20 4 16 2/7/2022 XXXXX-85 John 3/6/2011 10 3/6/2032 Medicaid 4 4 0 2/28/2022 XXXXX-85 John 3/6/2011 11 3/6/2032 Medicaid 20 2 18 3/24/2022 XXXXX-21 LAYTON 10/3/2010 11 10/3/2031 AmeriHealth - Medicaid 4 4 0 20 3 17 7/22/2022 XXXXX-3A Carmen 12/24/2003 18 12/24/2024 Healthy Blue - NCHC Medicaid 4 4 0 20 2 18 4/25/2022 XXXXX-66 Joslyn 9/18/2003 18 9/18/2024 NC Healthchoice 4 4 0 20 2 18 4/8/2022 XXXXX-15 Trey 6/30/2007 14 6/30/2028 Medicaid 20 4 16 6/16/2022 XXXXX-15 Trey 6/30/2007 14 6/30/2028 WellCare - Medicaid 4 4 0 4/4/2022 XXXXX-15 Trey 6/30/2007 15 6/30/2028 Trillium Medicaid 20 2 18 8/8/2022 XXXXX-50 Ashley 12/24/2004 17 12/24/2025 WellCare - Medicaid 20 18 2 6/3/2022 XXXXX-71 JACOB 10/7/2004 17 10/7/2025 BCBS NC 20 2 18 1/4/2022 XXXXX-86 Isabelle 10/28/2005 16 10/28/2026 Medicaid 4 2 2 20 8 12 8/1/2022 XXXXX-56 Camila 6/4/2006 15 6/4/2027 Medicaid 4 4 0 20 2 18 3/29/2022 XXXXX-00 Malyah 10/4/2002 19 10/4/2023 Carolina Complete Health - Medicaid 4 4 0 20 2 18 5/27/2022 XXXXX-9A Jayden 5/25/2007 15 5/25/2028 Medicaid 4 4 0 7/5/2022 XXXXX-4A Jariel 10/3/2005 16 10/3/2026 Medicaid 4 4 0 20 10 10 6/30/2022 XXXXX-3A Caelyn 8/8/2002 19 8/8/2023 Medicaid 20 12 8 4/21/2022 XXXXX-37 Jeremiah 7/12/2012 9 7/12/2033 Medicaid 4 2 2 20 2 18 5/31/2022 XXXXX-25 Ryan 7/3/2006 15 7/3/2027 Healthy Blue - Medicaid 4 4 0 20 4 16 6/28/2022 XXXXX-25 Ryan 7/3/2006 16 7/3/2027 Healthy Blue - Medicaid 20 6 14 8/16/2022 XXXXX-85 KELSEY 10/26/2004 17 10/26/2025 Medicaid 20 2 18 2/8/2022 XXXXX-78 Karizma 10/18/2002 19 10/18/2023 Healthy Blue - Medicaid 4 4 0 2/10/2022 XXXXX-25 Lola 7/26/2004 17 7/26/2025 WellCare - Medicaid 4 4 0 4/26/2022 XXXXX-86 Modesty 6/2/2005 17 6/2/2026 Medicaid 4 4 0 6/9/2022 XXXXX-46 MELISA 2/24/2007 15 2/24/2028 Carolina Complete Health - Medicaid 4 4 0 20 2 18 6/24/2022 XXXXX-5A Naudia 12/22/2005 16 12/22/2026 Medicaid 4 4 0 20 6 14 7/21/2022 XXXXX-5A Naudia 12/22/2005 16 12/22/2026 Trillium Medicaid 20 2 18 8/9/2022 XXXXX-44 Jaidyn 8/1/2009 12 8/1/2030 AmeriHealth - Medicaid 4 4 0 20 2 18 4/7/2022 XXXXX-55 Delilah Blue 12/29/2007 14 12/29/2028 Beacon Health Options 4 4 0 20 2 18 4/4/2022 XXXXX-51 Isabella 7/11/2005 16 7/11/2026 NC Healthchoice 4 4 0 20 24 -4 7/5/2022 XXXXX-51 Isabella 7/11/2005 17 7/11/2026 NC Healthchoice 4 2 2 20 8 12 8/15/2022 XXXXX-7A Josephine 9/6/2004 17 9/6/2025 Medicaid 20 2 18 1/6/2022 XXXXX-9A PAMELA 3/12/2003 18 3/12/2024 Medicaid 4 2 2 20 4 16 2/18/2022 XXXXX-69 Jacob 4/3/2009 12 4/3/2030 BCBS NC 20 2 18 3/1/2022 XXXXX-69 Jacob 4/3/2009 12 4/3/2030 WellCare - Medicaid 4 4 0 20 7 13 3/29/2022 XXXXX-74 Hannah 8/27/2010 11 8/27/2031 Medicaid 4 4 0 1/28/2022

Super User

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors