Forum Discussion
Using a filter to filter on several columns with similar values
Anonymous
To create a filter that scans each of the Items_Name columns and filters on the item, you can use Power BI’s built-in features. Let’s break down the steps:
Unpivot Columns:
- In Power Query Editor, select the columns Items_1_Name, Items_2_Name, Items_3_Name, and Items_4_Name.
- Right-click and choose Unpivot Columns. This will transform your data into a more manageable structure.
Create a New Column:
- In the transformed table, you’ll have two new columns: Attribute and Value.
- Rename these columns to something more meaningful (e.g., Item_Type and Item_Name).
Create a Slicer:
- Go back to your report canvas.
- Create a slicer using the Item_Name column.
- This slicer will allow you to filter on the items you want.
Apply the Filter:
- Use the slicer to select the specific items you want to filter.
- The visualizations in your report will automatically adjust based on the selected items.
Result:
- You now have a single slicer that filters across all the Items_Name columns, eliminating the need for separate filters.
Hi Anonymous,
Assuming I understood your requirements correctly, I believe you require a Master Item List that can be used to filter your existing tables. We can achieve this using the following steps -
Taking the below sample dataset as an example
- Create a reference (Master_Item_list) of the existing table, so that you can do your transformations on it without disturbing the original table (Samplel_ItemNames)
- Remove the quantity-related columns as they are not necessary for creating a master item list.
- Use the Unpivot Column in the Transform tab to translate data into attribute-value pairs, making it easy for us to create a master list
- You can remove the attribute column since that is not required.
Once the transformations are applied and tables are available for building visuals, complete the below steps -
- Create a relationship between the 2 tables, Sample_ItemNames and Master_Item_List on the Order_ID column. Keep the filter direction to both sides
- This will allow us to use the slicer created from Master_Item_List to filter your original table.
- Create the table and slicer visuals and voila! It should work.
Filtering examples
Let me know if this helps or if you have any other requirements!
Solution SupplierAnonymous
To create a filter that scans each of the Items_Name columns and filters on the item, you can use Power BI’s built-in features. Let’s break down the steps:
Unpivot Columns:
- In Power Query Editor, select the columns Items_1_Name, Items_2_Name, Items_3_Name, and Items_4_Name.
- Right-click and choose Unpivot Columns. This will transform your data into a more manageable structure.
Create a New Column:
- In the transformed table, you’ll have two new columns: Attribute and Value.
- Rename these columns to something more meaningful (e.g., Item_Type and Item_Name).
Create a Slicer:
- Go back to your report canvas.
- Create a slicer using the Item_Name column.
- This slicer will allow you to filter on the items you want.
Apply the Filter:
- Use the slicer to select the specific items you want to filter.
- The visualizations in your report will automatically adjust based on the selected items.
Result:
- You now have a single slicer that filters across all the Items_Name columns, eliminating the need for separate filters.
Thanks for the answer, this was also extremely helpful