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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
bmlweiler
New Member

Filter by List of Items greater than 500

I am creating a report where need to exclude a list of 632 items.  However when I attempt to create the filter to exclude these 632 items I receive the following message:

 

"The query contains a filter with too many values. For queries against this model and server, the maximum number of values allowed in a single filter is 500. Please reduce the number of selected values in the filter."

 

Unfortunately I am unable to reduce the number of items I need to exclude from this report and there are no unique attributes assocuated with these items that would allow me to reduce the number of values in the filter to fall below the limit of 500.  Does anyone have any recommendations on how to accomplish this filter?  

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @bmlweiler ,

I'd like to suggest you add a calculated column to group your records to 'include'/'exclude', then you only need to set an exclude filter on the new column to exclude records with 'exclude' tag.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @bmlweiler ,

I'd like to suggest you add a calculated column to group your records to 'include'/'exclude', then you only need to set an exclude filter on the new column to exclude records with 'exclude' tag.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

How would I go about creating a calculated column to exclude the 632 items?  Wouldn't this involve an If formula that would list out all 632 item to be excluded?  That would be an extremly large formula?  Not exactly an ideal solution.  

Hi @bmlweiler ,

That not means to configure all value in the calculated column, you can consider using text functions to do fuzzy match records to set the category of your records. (e.g.if statement with text function 'start with', 'end with', 'contains')

From SQL to DAX: String Comparison 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
helassal
Resolver II
Resolver II

@bmlweiler 

 

Since they are not needed on the report level, I suggest you remove them from the dataset alltogether using Power Query Editor.

Since you have a list of items you need to execlude, add this lookup list as a new query and then merge the original query with the new query using the "Left Anti" join, that will keep only the items not found in the lookup query.

 

LeftAnti.jpg

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.