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] ) )
                )
            )
    )
)
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Anonymous 

I'm havinf a difficulty picturing the situation. Would you please share a screenshot of the source table?

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!