Forum Discussion

diaz_edinson's avatar
diaz_edinson
Frequent Visitor
5 years ago
Solved

Multiple True / False filter expression

Hi,

I am trying to create a measure that sums the cost values, filtering by string type columns that contain certain parameters, however, although the measure theoretically has no errors, it is giving me wrong results, because it doesnt apply any of the filters. I tried to do it in three different ways as shown below, but none of them work properly. 

Next I show you what I did so that please help me

 

COST =
CALCULATE(
SUM(FACTS[Cost]),
CONTAINSSTRING(LOWER(FACTS[Campaign name]),"ff") = TRUE ||
CONTAINSSTRING(LOWER(FACTS[Creative]),"ff")=TRUE ||
CONTAINSSTRING(LOWER(FACTS[CONTENIDO]),"ff")=TRUE||
CONTAINSSTRING(LOWER(FACTS[Campaign name]),"pub")=TRUE &&
CONTAINSSTRING(LOWER(FACTS[Funnel Stage]),"do")=TRUE
)

 

in this case I have the following error:

The expression contains multiple columns, but in a True / False expression that is used as a table filter expression, only one column can be used.

so I included the filter statement:


COST2 =
SUMX(
FILTER(
FACTS,
(CONTAINSSTRING(LOWER(FACTS[Campaign name]),"ff")=TRUE) ||
(CONTAINSSTRING(LOWER(FACTS[Creative]),"ff")=TRUE) ||
(CONTAINSSTRING(LOWER(FACTS[CONTENIDO]),"ff")=TRUE) ||
(CONTAINSSTRING(LOWER(FACTS[Gerencia]),"pub")=TRUE) &&
(CONTAINSSTRING(LOWER(FACTS[Funnel Stage]),"do")=TRUE)
),
FACTS[Cost]
)

 

but it doesn't work properly either 😞


COST3 =
CALCULATE(
SUM(FACTS[Cost]),
FILTER(
FACTS,
CONTAINSSTRING(LOWER(FACTS[Campaign name]),"ff") = TRUE ||
CONTAINSSTRING(LOWER(FACTS[Creative]),"ff")=TRUE ||
CONTAINSSTRING(LOWER(FACTS[CONTENIDO]),"ff")=TRUE||
CONTAINSSTRING(LOWER(FACTS[Campaign name]),"pub")=TRUE &&
CONTAINSSTRING(LOWER(FACTS[Funnel Stage]),"do")=TRUE
)
)

 

Any ideas that can help me?

Need Help
  • diaz_edinson's avatar
    diaz_edinson
    5 years ago

    the reason I used CONTAINSSTRING is that the text contains the arguments "ff" or "do". but I solved it using this:

     

    Cost = 
VAR NESFF =
    CALCULATE(
        SUM(FACTS[Cost]),
        FILTER(
            FACTS,
            IF(
                CONTAINSSTRING(LOWER(FACTS[Campaign name]),"ff") ||
                CONTAINSSTRING(LOWER(FACTS[Creative]),"ff")  || 
                CONTAINSSTRING(LOWER(FACTS[Ad content]),"ff") ||
                CONTAINSSTRING(LOWER(FACTS[Ad content]),"PUB"), 1, 0
            )=0
        )
    )
RETURN
NESFF

     

    Regards

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Please provide sample data in usable format (not as a picture).

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI diaz_edinson,

    I'm not so sure why you keeping used CONTAINSSTRING functions. In fact, you can write a filter function to append with these conditions. Please try to use the following measure formula if it helps:

    COST =
CALCULATE (
    SUM ( FACTS[Cost] ),
    FILTER (
        ALLSELECTED ( FACTS ),
        LOWER ( FACTS[Campaign name] ) = "ff"
            || LOWER ( FACTS[Creative] ) = "ff"
            || LOWER ( FACTS[CONTENIDO] ) = "ff"
            || AND (
                LOWER ( FACTS[Campaign name] ) = "pub",
                LOWER ( FACTS[Funnel Stage] ) = "do"
            )
    )
)

    If above not works, can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng

    • diaz_edinson's avatar
      diaz_edinson
      Frequent Visitor
      5 years ago

      the reason I used CONTAINSSTRING is that the text contains the arguments "ff" or "do". but I solved it using this:

       

      Cost = 
VAR NESFF =
    CALCULATE(
        SUM(FACTS[Cost]),
        FILTER(
            FACTS,
            IF(
                CONTAINSSTRING(LOWER(FACTS[Campaign name]),"ff") ||
                CONTAINSSTRING(LOWER(FACTS[Creative]),"ff")  || 
                CONTAINSSTRING(LOWER(FACTS[Ad content]),"ff") ||
                CONTAINSSTRING(LOWER(FACTS[Ad content]),"PUB"), 1, 0
            )=0
        )
    )
RETURN
NESFF

       

      Regards