Need way to measure/filter with multiple values in multiple columns

[MTracy]

I've been using PBI for a few months now through self-training (videos, blogs, articles, and this forum).   I'm stuck on how to create a way to filter my data table (let's call that table 'CP') based on the occurence of multiple alphanumeric, two-character codes in each row.   The current data set has between one and six codes per row.   The codes appear first in a single column separated by commas, then repeat singly in following columns.    Also, there are around 35 unique codes in the current data set, with the potential of having hundreds in the future.  I'm not sure if the solution needs to center on the combined CSV-like column, or the individually-listed ones.   This screenshot should clarify the table & columns:

The very first column (out of view in screenshot) lists unique-valued data (like a serial #) that is the primary focus of the table.  I'd like to have a way to filter those based on the presence of each EX code.  (Ex:  show me all Serial #'s that have "PZ or CK" codes)  I can't figure out how to do this, but I suspect the solution needs to be a measure rather than a column.  Creating an Index table didn't work out well, as it was hard to link the relationship back.   Can anyone think how to create this?   

Thanks in advance for all advice!

[Greg_Deckler · Accepted solution]

This might be a situation where you could employ the "disconnected table trick". I just published a blog article on it here:

https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

What I am thinking is that you could have a disconnected table of all of your codes and use measures that employ SEARCH or FIND that return true/false based upon whether you can find/search the particular code in each column.

This may not be the way to go.

An alternative and perhaps better approach would be to use Power Query to pivot/unpivot your data such that each item has as many rows as codes. So, you would have something like:

Column, Column1, Column2, Code

blah, blah, blah, PJ

blah, blah, blah, PZ

blah, blah, blah, TC

foo, foo, foo, YT

foo, foo, foo, PJ

foo, foo, foo, TC

Then a standard slicer *should* do the trick.

[MTracy]

Thanks, @Greg_Deckler - your answers in this forum have always been illuminating!

I contemplated the 'disconnected table' method, but found the whole solution a bit complicated, despite already having a disconnected table listing every code.

I haven't unpivoted before, yet I understand the process.   I can see unpivoting the table as a solution to this, but will doing so affect already existing visuals?   I'd hate to lose all their configurations because of the unpivot.   Again, thanks.

[Greg_Deckler]

Well, unfortunately I'm not sure how it would affect your visuals or your measures or calculated columns for that matter because I have no idea what those configurations and formulas are. The idea I have in my head is that all of your columns stay the same except for the code columns. However, if you are doing things like a SUM on some column in a visual and before you had one row and now you have three or four, you would have to switch that to something like an average. Only advice I can give is to make a copy of your PBIX, switch up the query and compare side-by-side and fix anything that has changed. 

[MTracy]

Unpivoting just those columns did model the data in such a way that I could finally create visualization types that I wanted, but it did throw off some of the pre-existing visuals due to the way the table changed.   I solved that issue by simply importing a duplicate of my existing table, and only unpivoting the needed columns in that table, then building my visuals based only off that table... best of both worlds in the end!

Thanks to @Greg_Deckler for bringing the solution to my problem!

[Greg_Deckler]

Awesome, glad to help!

[Anonymous]

Hi Gred,

Please see if you can help me. In my scenario I have two Sharepoint list multivalues column like ";#India;#China;#Japan;#Korea" and another column like ";#Gold;#Silver;#Iron". 

I need to filter rows that contains "Silver and China" or just "China". Do you have any idea.

Thanks,

Augusto