Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table containing listings of of our sales, and each row contains (among other data) the product(s) sold in that sale.
I have a slicer containing various types of products. The idea is that the table will only show those sales that include one or more of the products selected in the slicer.
But the product names are many, and the product types are few. For example, here are some sample product names:
Dining Table (6) Dining Table (8) Card Table Coffee Table (Pine) Bedside Table Chair (Wooden) Chair (Plastic) Collapsible Chair Wardrobe (Wood) Wardrobe (Built-In) Wardrobe (Small) Couch (Leather) Couch (Fabric)
One of the values selectable in the slicer is 'Table'. So if the user chooses 'Table', I want to show all of the values in the table above which contain the string 'table'. I'm using a simple search to see if products should be shown - if the slicer value is found in the product name, then I show sales featuring that product. Great.
But if the user chooses multiple values in the slicer, I'm in trouble. For example, they might choose 'Table' and 'Chair'. In that case, I want to show all sales featuring products that contain either the string 'Table' or the string 'Chair'.
I can get all of the selected values in the slicer using CONCATENATEX, but there's no way using DAX that I can iterate through them, checking (for each row in the database table) whether each of the selected values can be found within the product name.
I thought of creating a table at runtime with the values selected in the slicer - but even if I do so, I can't use 'CONTAINS' because it goes the wrong way - I want to search for each row in the table in a single product name, rather than search for a single product name in each row in the table.
Thanks for any help.
Solved! Go to Solution.
If you have a table that contains your product types (not related to your product table), then you can write a measure that checks if the product matches any of the selected ones:
Visible = MAXX( VALUES(ProductTypes[Type]), MAXX(Products, IF( SEARCH([Type], Products[Product], 1, 0) > 0, 1, 0 ) ) )
Then just set the visual level filter to only show [Visible] = 1.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |