cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gsed99 Helper II
Helper II

Calculated field with filter excluding specificrecords

Hello,

I am trying to modify a calculated field and add a filter that will exclude certain opportunity numbers from the summation.

The formula works without the last filter, but I need to exclude certain records from the reporting.

Any help would be grealy appreciated!
Thanks,
Greg

 

 

CY # SQO (All) =
CALCULATE (
COUNT ( 'Marketing Snapshot'[SQODate] ),
FILTER (
ALL ( 'Marketing Snapshot'[SQOYear] ),
'Marketing Snapshot'[SQOYear] = [Max SQO Year]
),
FILTER (
ALL ( 'Marketing Snapshot'[DateStampMonth] ),
'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth]
),
FILTER (
ALL ( 'Marketing Snapshot'[OpportunityNumber] ),
'Marketing Snapshot'[OpportunityNumber] NOT IN {"O751700","O761393"})

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Community Champion
Community Champion

Re: Calculated field with filter excluding specificrecords

Hi @gsed99

 

The negation of

<X> IN <Y>

is

NOT <X> IN <Y>

 

Also, since your 3rd filter argument within CALCULATE doesn't reference any measures etc, you can get rid of FILTER (ALL(...)) and get the same result.

 

This expression should work:

CY # SQO (All) =
CALCULATE (
    COUNT ( 'Marketing Snapshot'[SQODate] ),
    FILTER (
        ALL ( 'Marketing Snapshot'[SQOYear] ),
        'Marketing Snapshot'[SQOYear] = [Max SQO Year]
    ),
    FILTER (
        ALL ( 'Marketing Snapshot'[DateStampMonth] ),
        'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth]
    ),
    NOT 'Marketing Snapshot'[OpportunityNumber] IN { "O751700", "O761393" }
)

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
indhu Helper III
Helper III

Re: Calculated field with filter excluding specificrecords

hey @gsed99,

 

can't this be set in visual, page or report level filters? had a thought seeing your post but not sure if that will work for you. 

 

 

OwenAuger Community Champion
Community Champion

Re: Calculated field with filter excluding specificrecords

Hi @gsed99

 

The negation of

<X> IN <Y>

is

NOT <X> IN <Y>

 

Also, since your 3rd filter argument within CALCULATE doesn't reference any measures etc, you can get rid of FILTER (ALL(...)) and get the same result.

 

This expression should work:

CY # SQO (All) =
CALCULATE (
    COUNT ( 'Marketing Snapshot'[SQODate] ),
    FILTER (
        ALL ( 'Marketing Snapshot'[SQOYear] ),
        'Marketing Snapshot'[SQOYear] = [Max SQO Year]
    ),
    FILTER (
        ALL ( 'Marketing Snapshot'[DateStampMonth] ),
        'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth]
    ),
    NOT 'Marketing Snapshot'[OpportunityNumber] IN { "O751700", "O761393" }
)

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

gsed99 Helper II
Helper II

Re: Calculated field with filter excluding specificrecords

hey @indhu,

I would actually need this to be done in the formula becuase my report is showing other metrics, so if I filter it on the visual, it will be limited those other metrics.

Thanks,

Greg

gsed99 Helper II
Helper II

Re: Calculated field with filter excluding specificrecords

Thanks @OwenAuger, that worked!!!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors