How to avoid duplicated rows?

[anwilkins]

Prequil - Do not ask for data, I cannot give it. If you need it to be helpful, scroll on to another question as I can do without the rude comments I often receive when requesting help from this group. Unfortunately,  my company rules prevent attachments and creating a dummy dataset for this would take days but if you can translate the patient data into sales to a customer who shops at several stores from several reps, maybe you can help. Thanks in advance.

The issue:

I am getting duplications in the rows when a patient during the give date range has a change in any of these:

   The Facility, The Doctor, The insurance Carrier

The desired output should be a single line that shows the following:

   Last Facility visited where the specified visit type (cpt code) was applied

   The Doctor they saw on that last visit

   The patients name

   The date of that last visit

   The Carrier on that last visit

   The allowed / applied & remaing units allowed by that Last Insurance Carrier

EX:

Facility                          / Dr        / Patient / LastVisit     / Current Carrier     / Allowed / Used / Remaining

New Hanover Office / Martha / Larry J / 08/12/2022 / Trillium Medicaide / 24          /   6      / 18

I Have written a bit of dax that calculates:

  * the allowed visits (units) a patient can have according to insurance rules,

  * the used (applied units) and

  * the remaining (remaining units

1) First step was to build a new column with the rules indicating how many units are allowed per insurance carrier based on the patient age

*C-Allowed =

IF(MERGE_BillingSummary[Current Insur Carrier] in {"AmeriHealth Caritas North Carolina", "AmeriHealth - Medicaid", "AmeriHealth - NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] > 20, 8,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"AmeriHealth Caritas North Carolina", "AmeriHealth - Medicaid", "AmeriHealth - NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] < 21, 16,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Carolina Complete Health - Medicaid", "Caolina Complete – NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] > 20, 24,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Carolina Complete Health - Medicaid", "Caolina Complete – NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] < 21, 24,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Healthy Blue - Medicaid",  "Healthy Blue – NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] > 20, 8,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Healthy Blue - Medicaid", "Healthy Blue – NCHC Medicaid"} && MERGE_BillingSummary[Patient Age] < 21, 12,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"United Healthcare"} && MERGE_BillingSummary[Patient Age] > 20, 99,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"United Healthcare"} && MERGE_BillingSummary[Patient Age] < 21, 99,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"WellCare"} && MERGE_BillingSummary[Patient Age] > 20, 20,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"WellCare"} && MERGE_BillingSummary[Patient Age] < 21, 20,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Alliance - Medicaid"} && MERGE_BillingSummary[Patient Age] > 20, 16,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Alliance - Medicaid"} && MERGE_BillingSummary[Patient Age] < 21, 8,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"AMI IPRS", "ASOUD IPRS", "ASOUD-Trillium IPRS", "ASTIM IPRS","ASTER IPRS", "IPRS"} && MERGE_BillingSummary[Patient Age] > 20, 12,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"AMI IPRS", "ASOUD IPRS","ASTIM IPRS", "ASOUD-Trillium IPRS", "ASTER IPRS", "IPRS"} && MERGE_BillingSummary[Patient Age] < 21, 24,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Trillium Medicaid", "Medicaid"} && MERGE_BillingSummary[Patient Age] > 20, 24,

IF(MERGE_BillingSummary[Current Insur Carrier] in {"Trillium Medicaid", "Medicaid"} && MERGE_BillingSummary[Patient Age] < 21, 24,

IF(MERGE_BillingSUMMARY[Current Insur Carrier] in {"**No Insurance Carrier", "ABC Grant", "Adolescent Disaster Grant", "Adult Disaster Grant", "Aetna", "All Savers UHC"} && MERGE_BillingSUMMARY[Patient Age] <0, 0)))))))))))))))))

2) The I wrote the following 3 measures:

   ** Allowed Units =   CALCULATE(MAX(MERGE_BillingSUMMARY[*C-Allowed]))

   ** Applied Units = CALCULATE(SUM(MERGE_BillingSUMMARY[Units]),

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

   ** Remaining Units = MERGE_BillingSUMMARY[*M-Allowed Units] - MERGE_BillingSUMMARY[*M-Applied Units]

I hope someone can tell me how to adjust my dax so things like a change in facility, doctor or carrier dont create a new line....especially when the last qualifying appointment occured outside of the date rang

 

[vanessafvg · Accepted solution]

Hi there ok so without data im just gonna wing it but i think what you need to do is;

create a measure on your date ie

last appointment = max(last apt)

then replace this  measure with the date on your matrix

change your other measures to 

change both your  

Allowed Units =
VAR maxdte =
MAX ( [last apt] )
RETURN
CALCULATE (
MAX ( MERGE_BillingSUMMARY[*C-Allowed] ),
FILTER ( 'table'[last apt], 'table'[last apt] = maxdte )
)

Applied Units =
VAR maxdte =
MAX ( [last apt] )
RETURN
CALCULATE (
SUM ( MERGE_BillingSUMMARY[Units] ),
FILTER (
MERGE_BillingSUMMARY,
MERGE_BillingSUMMARY[CPT Code]
IN {
"90791",
"90832",
"90834",
"90837",
"90839",
"90840",
"90846",
"90847",
"90853"
}
),
FILTER ( 'table'[last apt], 'table'[last apt] = maxdte )
)

[anwilkins]

Worked like a charm! Thank you so much for the assistance!

[vanessafvg]

great!

[vanessafvg]

On your comment about data, please understand it is difficult to give  good solutions without data.  Even if you can't come up with the data, if you ask questions a lot dummy data might the best way forward, sites like mockaroo make it easy to create sample data,  so dont take it personally when people ask for data, they just trying to give you the best solution and also not waste a lot of their time.

Howevever I will attempt to assist you with some suggestions.

Your code is hard to read.   I really do recommend you use a switch() statement if using dax for readablity when you have multiple if statements.  Also if you are using imported mode and you can do your transformations in Power Query i would push the logic for that column c-allowed to there into power query not doing it in DAX through by adding a custom column.

so just so i understand  clearly for a given date range you need the last record of a patient?  But depending on the date range that might differ so you need to keep all records of all patients?  Or do you only ever need the last record?

[anwilkins]

Thanks for responding. I sent a PM with additional information that may help.