cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gsed99 Regular Visitor
Regular Visitor

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 Super Contributor
Super Contributor

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



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

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
indhu Member
Member

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 Super Contributor
Super Contributor

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



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

Proud to be a Datanaut!




View solution in original post

gsed99 Regular Visitor
Regular Visitor

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 Regular Visitor
Regular Visitor

Re: Calculated field with filter excluding specificrecords

Thanks @OwenAuger, that worked!!!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 523 members 4,294 guests
Please welcome our newest community members: