Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

IF with multiple Contains

Similar to the below, I'm trying to make an IF statement with Contains but I am trying to count the times 2 (or more) affiliates appear in a customers click path together.   IF CONTAINS WITH MULTIP...
AND
CONTAINS
COUNT
if
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    Are you looking for something like this?

    FilterMeasure = 
COUNTROWS ( 
    FILTER ( 
        Orders,
        VAR String = Orders[Path_Taken]
        VAR Items = SUBSTITUTE ( String, " -> ", "|" )
        VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T = GENERATESERIES ( 1, Length, 1 )
        RETURN
            ISEMPTY ( 
                EXCEPT ( 
                    VALUES ( Stations[Station] ) ,
                    SELECTCOLUMNS ( T, "@Station", PATHITEM ( Items, [Value] ) )
                )
            )
    )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

tamerj1 

 

No problem, the below looks more readable than my original example.

 

So there are two tables that contain these columns and rows, they're copies of eachother, the original has more columns and the duplicate just has the necessary columns.. The taxonomy4 coloumn is the affiliate list. The path taken column is the start to finish journey taken by the user.

The tables aren't linked at all, the duplicate is only for the splicer list as it wouldn't work when they were selected from the same table. But that selection just gets converted into a string so not sure if that'll be an issue.

 

Then the contains goes over the path_taken columns in the original table. 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Anonymous 
Are you looking for something like this?

FilterMeasure = 
COUNTROWS ( 
    FILTER ( 
        Orders,
        VAR String = Orders[Path_Taken]
        VAR Items = SUBSTITUTE ( String, " -> ", "|" )
        VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T = GENERATESERIES ( 1, Length, 1 )
        RETURN
            ISEMPTY ( 
                EXCEPT ( 
                    VALUES ( Stations[Station] ) ,
                    SELECTCOLUMNS ( T, "@Station", PATHITEM ( Items, [Value] ) )
                )
            )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    tamerj1 

    Works perfectly, thank you so much for your help!