Forum Discussion

cn4422's avatar
cn4422
Helper V
1 year ago
Solved

NOT(CONTAINSSTRING) - multiple values

Hi, I'm trying to implement a measure with NOT(CONSTAINSSTRING) with 2 values, as in the following example:   Test = CALCULATE( SUM('GA, FB, Spend'[spend]), 'GA, FB, Spend'[account_name] = "DE...
  • johnt75's avatar
    johnt75
    1 year ago

    I think you might have the nesting wrong. What you currently have is

    Test =
CALCULATE (
    SUM ( 'GA, FB, Spend'[spend] ),
    'GA, FB, Spend'[account_name] = "DE",
    CONTAINSSTRING ( 'GA, FB, Spend'[campaign], "Gemischt*" ),
    NOT (
        CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR01*" )
            && NOT ( CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR02*" ) )
    )
)

    If you're trying to exclude both PR01 and PR02 try

    Test =
CALCULATE (
    SUM ( 'GA, FB, Spend'[spend] ),
    'GA, FB, Spend'[account_name] = "DE",
    CONTAINSSTRING ( 'GA, FB, Spend'[campaign], "Gemischt*" ),
    NOT CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR01*" )
        && NOT CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR02*" )
)
johnt75's avatar
johnt75
Super User
1 year ago

I think you might have the nesting wrong. What you currently have is

Test =
CALCULATE (
    SUM ( 'GA, FB, Spend'[spend] ),
    'GA, FB, Spend'[account_name] = "DE",
    CONTAINSSTRING ( 'GA, FB, Spend'[campaign], "Gemischt*" ),
    NOT (
        CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR01*" )
            && NOT ( CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR02*" ) )
    )
)

If you're trying to exclude both PR01 and PR02 try

Test =
CALCULATE (
    SUM ( 'GA, FB, Spend'[spend] ),
    'GA, FB, Spend'[account_name] = "DE",
    CONTAINSSTRING ( 'GA, FB, Spend'[campaign], "Gemischt*" ),
    NOT CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR01*" )
        && NOT CONTAINSSTRING ( 'GA, FB, Spend'[ad_group_name], "PR02*" )
)
cn4422's avatar
cn4422
Helper V
1 year ago

johnt75 Ah, thank you very much, that did the trick! ðŸ‘Œ