Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pentanol
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.

9 REPLIES 9
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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. 

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?

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

 


Regards
Zubair

Please try my custom visuals

@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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

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.

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.