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 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.
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