Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Multiple Boolean columns in slicer

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

Capture.PNG

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

View solution in original post

7 REPLIES 7
v-xuding-msft
Community Support
Community Support

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"))

1.PNG

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.

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

Anonymous
Not applicable

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

Cmcmahan
Resident Rockstar
Resident Rockstar

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 😄

Anonymous
Not applicable

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.