cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pentanol Frequent Visitor
Frequent Visitor

Large number of filters for a column

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.

8 REPLIES 8
Super User
Super User

Re: Large number of filters for a column

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 Frequent Visitor
Frequent Visitor

Re: Large number of filters for a column

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?

Super User
Super User

Re: Large number of filters for a column

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 Frequent Visitor
Frequent Visitor

Re: Large number of filters for a column

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!

Super User
Super User

Re: Large number of filters for a column

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

Smiley Wink

Pentanol Frequent Visitor
Frequent Visitor

Re: Large number of filters for a column

@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

Pentanol Frequent Visitor
Frequent Visitor

Re: Large number of filters for a column

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?

Super User
Super User

Re: Large number of filters for a column

Hi @Pentanol

 

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

 

Pentanol.png