Skip to main content
cancel
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

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

## Helpful resources

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors