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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors