Forum Discussion
Multiple Boolean columns in slicer
- 7 years ago
I would break it out into two unpivoted tables. No need to join them.
In the end, I would likely want to have 3 tables. One Customers table, that had a customer name, ID, address, etc. Then a table of unpivoted Product Preferences, which would be rows of {CustomerID, ProductName}. Then a table of unpivoted Application Preferences, which would be the same as the Products table - {CustomerID, ApplicationName}.
From there, all three tables should be related by CustomerID (or name, or whatever unique identifier you use), and you can put fields from all 3 into the same visual.
If you've got more fields for each Product/App, put all of that info into their own tables and add them into the data model, relating them to the appropriate Preference table.
I've created an example here with the sample data you provided.
Note that Product 2 and App1 don't appear in the table or the filters. This is because every customer responded "No" to them. If you created a seperate table for Products and Apps, and added relationships to the Preferred Products/Apps tables, you could filter using those tables, and every value would appear in the slicer.
Also, if you go into the query editor, you can see that I unpivoted and kept only the "Yes" Apps/Products with different methods. For Products I turned "No"s into nulls, then selected the Customer and unpivoted the other columns. For Apps I selected the App columns, unpivoted the selected columns, and then filtered out where the value was "No" before removing that column entirely. You can mix and match these filtering/unpivoting methods in whatever way makes the most sense for you data.
Hopefully this gives you a starting point for your actual data. If you're still having problems, please follow up here.
Your best bet here would be to unpivot your data in the query editor. Select the Customer Name column, and select Unpivot Other Columns in the Transform tab.
I would even go as far as to first change all the No's to nulls so that your table is just one customer and one product they've said "yes" to per row. Then you don't have to worry about filtering on yes vs no, since the rows with no's aren't even created. Heck, you could even remove the resulting "yes" column!
From there, you should be able to create a normal slicer on the new product name column.
Excellent idea... now to see if I can unpivot a table with 100k+ rows and 20+ boolean fields 😄