Filter by List of Items greater than 500

[bmlweiler]

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?  

[Anonymous · Accepted solution]

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

[bmlweiler]

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.  

[Anonymous]

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

[helassal]

@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.