Large number of filters for a column

[Pentanol]

Hi all,

I've been looking everywhere for a similar function in Power BI as the OLAP COM-add in Extension you can get for Excel without any luck. Basically I want to be able to copy and paste for example 100 material codes into a field and be able to filter for them to see how the sales are going for them for example. I know I can do it one by one with a slicer or on the filter screen, but for time efficiency purposes would prefer a quicker solution that finding and checking the box for over 100 product skus (this varies all the time, so I wouldn't be group them either as different products will be promoted each time).

Cheers in advance.

[Zubair_Muhammad]

Hi @Pentanol

This is very much possible. And the solution is "GROUPING or BANDING"

Check this file

Make a separate table for your selected list of SKUs. Lets say "SelectedSKUs"

Now create a Column as follows in your Complete SKU Table (Assuming this table's name is "Data")

Group =
IF (
    COUNTROWS ( FILTER ( SelectedSKUs, SelectedSKUs[SKU] = Data[SKU] ) )
        = 1,
    "MyGroup",
    "Others"
)

Now you add a slicer for "GROUP". Then select "MyGroup"

See the attached file

[Anonymous]

Hi Zubair - you file isn't opening for me and this function you posted didn't work in DAX. I have created a separate table with my selections that I want to group. However, I don't know how to add that group in the slicer option from my original source codes table. 

[Pentanol]

Just realised I can't add any extra data because I'm already connect to live data and I need this data for the analysis. There's no way for me to create the table first then get data or the other way round. DO you have a solution to this?

[Zubair_Muhammad]

Hi @Pentanol

You can create a separate table from Home Tab>>>Enter Data.
Here, you can copy paste data from Excel as well

[Pentanol]

@Zubair_Muhammad Thanks for your help! How do I make a separate table? I've been trying to create a table input so I can copy and paste different list of SKUs each time I want to see the financial performance of them?

Thanks

Andrew

[Ashish_Mathur]

Hi,

Assuming those 100 SKU's are in a different table, we can create a relationship from the SKU column of your main table to the SKU column of your SKU table.  Then we can use the RELATED() function in a calculated column of your mail table to bring over the SKU's from the SKU table.  We can then drag this new column to the filter section and filter on non blanks.

[Pentanol]

Hi Ashish,

Thanks for answering so quickly. I think you misunderstood my query. These skus are all in the same column. Say there's 1000 skus in one column and I want to filter for 430 sku of these skus, I don't want to be clicking the filter 430 times for each of these skus. Is there an easier way to filter all these skus?

[Ashish_Mathur]

Hi,

I dont think we can solve this using slicers or filters.  We will have to create a list of the 430 items that we want to filter on and then use the LOOKUPVALUE() function to fetch into your base data table, which SKU's are present in your smaller list.  We will then drag this column in the value area section and filter for non blank values.

[Pentanol]

Are you able to provide a video or a Power BI file of how it works? i.e. say one column of skus 1-1000 and you want to filter 1-430. Not sure whether your method will change if there is column 2 and 3 with data as well but say there is and you want those columns too. Thanks for your time!