I have data in which each record can have up to five codes. The codes are placed in fields called code1, code2, code3, code4 and code5. In these fields are codes from a list of about 100 possible codes. No code is repeated in the same record, and the which code field a code reside is irrelevant - when the codes were assigned, they were written to these fields alphabetically. A record can have from 0 to 5 codes.
My question: how do I create a calculation that looks at all the codes that exist in all the records and can use that for filtering? For example, I would like to drop this calculation on the filter shelf and be presented a list of active codes. When I select one, my result will be a list of records where that code shows up in any of the code fields: code1, code2, etc.
Given that any record may have multiple codes, any ideas on how to accomplish this? I do have the option of some data manipulation prior to bringing it into Tableau. I could, for example, separate them out so that each record becomes multiple records - one for each code. However, I'm afraid that the measures in each record will then be counted multiple times when looking at multiple codes or when my view does not depend on the codes.