Forum Discussion
Multiple Boolean columns in slicer
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.
Hi
Thanks for your reply, How to approach if I have table like above but table has products 1- 10 as columns and applications 1-3 as columns and i want sperate slicers for product and applications but want to filter the data in one table?
Shall I create 2 unpivot tables one for products and second one for applications and join two tables?
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.