cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MTracy Frequent Visitor
Frequent Visitor

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

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:

 

EX Codes.png

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

MTracy Frequent Visitor
Frequent Visitor

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

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.

Super User
Super User

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

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

MTracy Frequent Visitor
Frequent Visitor

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

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!

Super User
Super User

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

Awesome, glad to help!


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
AugustoGMAraujo Frequent Visitor
Frequent Visitor

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

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 208 members 2,169 guests
Please welcome our newest community members: