Using a filter to filter on several columns with similar values

[Anonymous]

I have a table that looks similar to the following data:

Name, Order_ID, Items_1_Name, Items_1_Quantity, Items_2_Name, Items_2_Quantity, Items_3_Name, Items_3_Quantity, Items_4_Name, Items_4_Quantity

Some rows will have blanks for some of the items columns depending on if the order has more than 1 type of item, and the same item can be in different columns. I'd like to create a filter that scans each of the Items_Name columns and filters on the item, so it only returns the items selected instead of needing to create 4 different filters to filter the same value. How can I do this?

[AnalyticsWizard · Accepted solution]

@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:

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

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

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

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

5. Result:

   • You now have a single slicer that filters across all the Items_Name columns, eliminating the need for separate filters.

[Bipin-Lala · Accepted solution]

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!

[Anonymous]

Thanks for the in depth answer, this was exctly what I was looking for. Very helpeful and useful 😄

[AnalyticsWizard · Accepted solution]

@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:

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

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

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

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

5. Result:

   • You now have a single slicer that filters across all the Items_Name columns, eliminating the need for separate filters.

[Anonymous]

Thanks for the answer, this was also extremely helpful