Need 3 measures to calculate the remaining allowed visits an insurance company will pay for

[anwilkins]

Original question modified. I cannot provide sample data so please do not ask. If you can't help without it, scroll past.

A patient visit is refered to as a Unit

A Therapy visit is determined by the following [CPT Codes] = 90791, 90832, 90834, 90837

A therapy patient gets a set number of allowed therapy sessions paid for then requires reauthorization.

The list below shows the number of Allowable visits in a fiscal year for patients under 20 and over 21 years of age per insurance.

Insurance Company                                                                                                                                     Patient Age / Allowed Units

"AmerHealth Caritas North Carolina","Carolina Complete Healthcare","Healthy Blue","Trillium Medicaid", "Medicaid" ,"AmeriHealth - Medicaid", "AmeriHealth - NCHC Medicaid"	< = 20	8
"AMI IPRS", "ASOUD IPRS", "ASOUD-Trillium IPRS", "ASTIM IPRS","ASTER IPRS"	< = 20	12
"Alliance - Medicaid"	< = 20	52
"Carolina Complete Healthcare","Healthy Blue"	>= 21	12
"AmerHealth Caritas North Carolina","Trillium Medicaid", "Medicaid","AmeriHealth - Medicaid", "AmeriHealth - NCHC Medicaid"	> = 21	16
"AMI IPRS", "ASOUD IPRS","ASTIM IPRS", "ASOUD-Trillium IPRS", "ASTER IPRS"	> = 21	24
"Alliance - Medicaid"	> = 21	52
	N/A
"**No Insurance Carrier", "ABC Grant", "Adolescent Disaster Grant", "Adult Disaster Grant", "Aetna", "All Savers UHC", "United Healthcare"	99	99

My data table contains the following columns: 

Patient ID / Patient Name / Patient Age / Service Date / Insurance Carrier / CPT Code

I need:

   Measure 1 - Calculate Allowed Units based on the criteria of CPT Code, Insurance Carrier and Patient Age

   Measure 2 - Calculate the number of Allowable Units that have been used thus far (example, an adult patient comes 6 times in a year but only 4 visits use the therapy CPT codes so I want the column to show the 4

   Measure 3 - Calculate the remaining units (Allowed - Used = Remaining)

****I am almost there using columns however I get multiple lines for patient which requires a manual deduction. See Image:

Thanks to anyone who can assist with the measures

[lbendlin]

Measures are calculated across a filter context. You cannot add a measure to a column, you can only add it to an aggregation function over that column.

You may also want to look into using SWITCH(TRUE(),...) instead of these nested IF() statements.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[anwilkins]

Thanks for responding. I am not sure what the format should be for the Switch Statement so perhaps you could provide an example as I gave all the rules (i.e. ages and allowed visits / units). As for providing sanitized data, corporate policy prevents it. 

[anwilkins · Accepted solution]

I resolved on my own with 4 steps

Step 1 -  I created a calculated column that laid out the rules related to the Allowed Units

Step 2-  I created Measure 1 - Allowed Units = CALCULATE(MAX(MERGE_BillingSUMMARY[*C-Allowed]))

Step 3 - Measure 2 - Applied Units = CALCULATE(SUM(MERGE_BillingSUMMARY[Units]),

FILTER(MERGE_BillingSUMMARY,MERGE_BillingSUMMARY[CPT Code] IN {"90791", "90832", "90834", "90837", "90839", "90840", "90846", "90847", "90853"}))

Step 4 - Measure 3 - Remaining Units = MERGE_BillingSUMMARY[*M-Allowed Units] - MERGE_BillingSUMMARY[*M-Applied Units]

Result yeilds as expected

[lbendlin]

"As for providing sanitized data, corporate policy prevents it. "

Can't help you without sample data.