topic Re: Multiple True / False filter expression in Developer

Multiple True / False filter expression

diaz_edinson — Thu, 08 Apr 2021 23:25:58 GMT

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?

Re: Multiple True / False filter expression

lbendlin — Mon, 12 Apr 2021 01:11:10 GMT

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

Re: Multiple True / False filter expression

Anonymous — Wed, 14 Apr 2021 02:49:48 GMT

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

Re: Multiple True / False filter expression

diaz_edinson — Fri, 16 Apr 2021 13:12:17 GMT

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