cancel
Showing results for
Did you mean:
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
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!

4 REPLIES 4
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.

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!

Highlighted
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

Regular Visitor

## Re: Calculated field with filter excluding specificrecords

Thanks @OwenAuger, that worked!!!

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 60 members 1,304 guests
Recent signins: