Forum Discussion
How to avoid duplicated rows?
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 )
)
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?
Thanks for responding. I sent a PM with additional information that may help.