How to create custom keyword based filter tiles so that click on each tile would filter the data

[duc1986]

Hello i have below data. i have home page created with multiple pie charts and bar graphs. since i have type of product column with variety of names which is not standard and varies by each row. i want to create first custom filter tile where i can create just keywords tile which contains a good combination of these types e.g. one standard can be toys, machine. after that clicking on any of these keyword tiles should filter the data that it suggest. for example. if i click on toys,machines it should also show the field with just Machine because we have included machine keyword in the keyword "toys, machines". and when i select particular keyword filter on tile, the entire page including all graphs and pie chart should get filtered and show only data for e.g. toys, machines. can anyone help here please?

Product list	type of product	date of purchase	Date of repeat purchase	name of the purchaser	Location	total sale	Total profit	Total no of item
ABC	toys, machines	23-06-2024	25-06-2024	XYZ	US	10000	500	100
XYC	Machines	25-06-2024	25-06-2024	ABC	EU	1000000	10000	50
ABC	Machine, pipes	23-06-2024	25-06-2024	XYZ	US	10000	500	100
XYC	Pipes, toys, bolts	25-06-2024	25-06-2024	ABC	EU	1000000	10000	50

[dk_dk · Accepted solution]

Hi @duc1986 ,

Looks like you would need to do some work on your data to make this possible. If you are able to edit the data in Power Query, you can do the following:

1. Create a reference of the query that includes your data.
2. Remove all columns except Product list and type of product (I am assuming product list is a unique ID for your data, if not, then keep a column that is a unique ID instead. If you do not have one you can create one in Power Query using Add Index Column.)
3. split type of product by delimiter
4. Select the product list column, and then unpivot other columns
5. Remove the Attribute column that is generated.
6. You now have a table that contains all possible pairings of product list (or another unique ID / index) and the values in type of product.
7. You can close and load this table into the model.
8. Configure relationships so that there is a connetion between your existing table and the table you just created, based on the Product list (or other ID /index columns) Make sure that the table you created filters the original table
9. You can now add the Value column from the table you just created to a slicer.
10. In the Slicer settings, set the style to "Tile".

Let me know if this works, or if you have any questions!

Best regards,

Daniel

[dk_dk · Accepted solution]

Hi @duc1986 ,

Looks like you would need to do some work on your data to make this possible. If you are able to edit the data in Power Query, you can do the following:

1. Create a reference of the query that includes your data.
2. Remove all columns except Product list and type of product (I am assuming product list is a unique ID for your data, if not, then keep a column that is a unique ID instead. If you do not have one you can create one in Power Query using Add Index Column.)
3. split type of product by delimiter
4. Select the product list column, and then unpivot other columns
5. Remove the Attribute column that is generated.
6. You now have a table that contains all possible pairings of product list (or another unique ID / index) and the values in type of product.
7. You can close and load this table into the model.
8. Configure relationships so that there is a connetion between your existing table and the table you just created, based on the Product list (or other ID /index columns) Make sure that the table you created filters the original table
9. You can now add the Value column from the table you just created to a slicer.
10. In the Slicer settings, set the style to "Tile".

Let me know if this works, or if you have any questions!

Best regards,

Daniel

[duc1986]

Thank you so much! i will just reiterate what i am expecting. i want a tile filter where by clicking on any tile (e.g. Machine) will search and show the graphs filtered with the fields where ever the type of product contains Machine key word. it should include e.g. Machine and toys also. so basically i want to assign search funcationality to tile button where each button has specific keyword. e.g. Machine. and clicking on machine, it searches and filters all data on the page. do you think what you are suggesting will sort this out?

[dk_dk · Accepted solution]

Hi,

Yes, my solution should result in the functionality you intend to have. Essentially, what you are doing is splitting the comma separated keywords and assigning each keyword individually to the specific row. So any time you select a keyword, it will filter the original row where that keyword is mentioned (in the long comma separated list of keywords).

As an alternative, or if you are unable to modify the query in Power Query, you could look into the custom visuals and see if any of the text search slicers/filters work for your use case. Last time I tried those they were not really good with partial text matching, maybe it improved since.


If something is unlcear or if you are stuck with any steps let me know and I will try to help!