Multiple Boolean columns in slicer

[Anonymous]

I have multiple yes or No columns in a table, I want to use slicer on these columns and one row might have multiple yes values. See below  I want products 1to 10 in slicer and filter based on value yes, if i select product 1 or 3 or both  i want see customer 1. Any help on this would be great. Thanks

[v-xuding-msft]

Hi @Anonymous ,

I created a sample you can reference and download.

Firstly, you need to unpivot the table in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUapMLQaSefnIzFgdJEVGMGk8aoyxGQQiUVSZoEghqUVRZYphFRZFZsQoMidGkQVOR8UCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t, #"Product 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Product 1", type text}, {"Product 2", type text}, {"Product 3", type text}, {"Product 4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Then create a measure using the function of COUNTROWS.

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Value] = "yes"))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sonjaer]

hiiii,i tired the measure dax but it show customers who're using 1 or 2, not customers using both product1 and 2; is there any way to get the result above

[Anonymous]

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? 

[Cmcmahan · Accepted solution]

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.

[Cmcmahan]

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.  

[GrahamCrackers]

Excellent idea... now to see if I can unpivot a table with 100k+ rows and 20+ boolean fields 😄

[Anonymous]

Hi 

That is only a sample table in my case I have a lot of columns other than the company name, unpivot will not work. Is there any other way. Thanks