Forum Discussion
Best Modeling - Joining to multiple records without duplicating/multiplying the values
I resolved this by including the diagnosis in the main result data. Even though there are multiple records per lab event if there is more than one diagnosis to a patient, the measures are not duplicating their values.
I believe the true correct model would be to have a separate table for patients and a separate table with patientId, diagnosis. Then to relate Patient > PatientDiagnosis and relate "labEvent" to Patient. However, when I did this, regardless of the crossjoin and cardinality configuration of the joins/relationships, the measures were not calculating correctly so I had to resort to including the diagnosis in the main labEvent table and not use relationships.
Hello,
Thank you for your input (apologies for the delay in getting back to this). The calculated table would not suffice as all my slicers still need to be taken into account. Is there a way I can do something like a subquery that says "pull all the patients that have a diagnosis in ( table that holds patientid , diagnosis) " rather than say join the lab event data to the diagnosis data which would pull multiple records of each labevent if there were multiple diagnoses?
I resolved this by including the diagnosis in the main result data. Even though there are multiple records per lab event if there is more than one diagnosis to a patient, the measures are not duplicating their values.
I believe the true correct model would be to have a separate table for patients and a separate table with patientId, diagnosis. Then to relate Patient > PatientDiagnosis and relate "labEvent" to Patient. However, when I did this, regardless of the crossjoin and cardinality configuration of the joins/relationships, the measures were not calculating correctly so I had to resort to including the diagnosis in the main labEvent table and not use relationships.