Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to 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
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
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
211 | |
82 | |
61 | |
61 | |
60 |