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",
CONTAINSSTRING('GA, FB, Spend'[campaign], "Gemischt*"),
NOT(CONTAINSSTRING('GA, FB, Spend'[ad_group_name], "PR01*") && NOT(CONTAINSSTRING('GA, FB, Spend'[ad_group_name], "PR02*")
)))

 

The first NOT(CONTAINS) filter is working but the second one is being ignored.

 

Any idea what's wrong with this measure?

Thx in advance! 🙂

  • 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*" )
)

2 Replies

  • 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*" )
)