Multiple True / False filter expression

[diaz_edinson]

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?

[Anonymous]

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 · Accepted solution]

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

[lbendlin]

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