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

[anwilkins]

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.

[lbendlin]

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

[anwilkins]

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?

[lbendlin]

no sample data = no further help possible.

[anwilkins]

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

[lbendlin · 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]))