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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
barbforsman
Resolver I
Resolver I

report level filter vs. filter on query

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"))

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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?

This is the report level filter

report level filter.JPG

 

And this is the filter on the query:

query filter.JPG

 

Help will be much appreciated!

 

Anonymous
Not applicable

Did you try this option? This works for me.

 

Pivot.png

 

And then apply the filter

 

Filter.png

Filter.png

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 thisStatus.JPG

 

 

Anonymous
Not applicable

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"))

 

This is the report level filter

report level filter.JPG

 

And this is the filter on the query:

query filter.JPG

 

Help will be much appreciated!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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