report level filter vs. filter on query

[barbforsman]

I have a Report Level Filter on a report on my CRM Salesorders, "Name does not contain Cancel".  I want to change that so the filter is on the query itself, rather than on the report, but my values change by up to 2K if I do that. 

Why does putting the filter on the Query still allow some orders to come through to the report with "Cancel" in the name?  Why do I still need the Report Level Filter to filter them all out?

Values with filter on the query:

2016 = 61.57

2017 = 104.91

2018 = 48.49

Values with filter on Report Level Filter

2016 = 59.12

2017 = 103.23

2018 = 48.49

[Anonymous]

Could you please share what is the exact value in the column ( is it just 'Cancel' or value contains 'Cancel') and also share what filter you have used in Query level?

[barbforsman]

This is the report level filter

And this is the filter on the query:

Help will be much appreciated!

[Anonymous]

Did you try this option? This works for me.

And then apply the filter

[barbforsman]

Thank you - I tried this but my canceled orders are already filtered out. 

I do need to filter out orders that have Cancel in the name field as we do end up with a few orders in an Invoiced Status but end up Canceled.  I've included a screenshot from an advanced query in CRM showing this

[Anonymous · Accepted solution]

Got it. Power Query is case sensitive. You have given does not contain "Cancel" so records with "Cancel" will filtered, but not "CANCEL". Please use the below formula to make sure all CANCEL/ Cancel gets filtered.

= Table.SelectRows(#"Changed Type", each not Text.Contains(Text.Upper ([Status]), "CANCEL"))

[barbforsman]

This is the report level filter

And this is the filter on the query:

Help will be much appreciated!