Forum Discussion

gsed99's avatar
gsed99
Icon for Helper III rankHelper III
8 years ago
Solved

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

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

4 Replies

  • indhu's avatar
    indhu
    Icon for Helper III rankHelper III
    8 years ago

    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. 

     

     

    • gsed99's avatar
      gsed99
      Icon for Helper III rankHelper III
      8 years ago

      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

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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