The challenge for me in creating a lookup variable this time is that there is no single key between my 2 datasets. However, if I could use 2 fields to define my source and destination expressions, then I would be able to accurately link.
For example, in this case, the Patient_ID field is shared in both datasets, but it alone is not necessarily unique since different facilities could have the same Patient_ID for a different people . Thus, to avoid this error, I would need to use patient Patient_ID
along with Facility_ID in order to establish a key between my two datasets.
Thus the question: Is it possible to create a lookup variable that defines source and destination expressions using 2 fields from each dataset in order to establish the relationship between two datasets?
Based on my research, we cannot specify two fields as source_expresion or destination_expression in a lookup expression. If you want to specify two fields to establish a key between two datasets, I suggest that you can use two lookupset functions like below
to return the correspond results, then use custom code (Right-click report>Report Properties>Code) to write a function to return the same values from the two arrays.
=join(lookupset(Fields! Patient_ID.Value,Fields! Patient_ID.Value,Fields!col.Value,"DataSet2"),",")
=join(lookupset(Fields! Facility_ID.Value,Fields! Facility_ID.Value,Fields!col.Value,"DataSet2"),",")
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.