IF with multiple Contains

[Anonymous]

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 MULTIPLE VALUES 

E.g.

Selection1= HUKD

Selection2= TCB

OrderID	Path_Taken
Order1	Step1 -> Step2 -> HUKD -> TCB
Order2	Step1 -> Step2 -> HUKD -> TCB
Order3	Step1 -> Step2 -> HUKD -> TCB
Order4	HUKD-> Step2 -> HUKD -> Step4
Order5	Step1 -> Step2 -> Step3 -> TCB

The count would equal 3 as they appear in the paths together 3 times.

Stringer Code:

StringerT4 =

CONVERT(SELECTEDVALUE(Steps_AffiliateOnly[taxonomy_4]),STRING)

Mutual counter Code:

Mutual = 

IF(

AND(

CONTAINS(Steps_AffiliateOnly, Steps_AffiliateOnly[path_taken],Steps_AffiliateOnly[StringerT4]),(

CONTAINS('Steps_AffiliateOnly_2','Steps_AffiliateOnly_2'[path_taken],'Steps_AffiliateOnly_2'[StringerT4_Second])))

,           COUNT(Steps_AffiliateOnly[order_id]))

Logically it makes sense to me, but the output is not what i'm expecting.

Thanks in advance for any help

[tamerj1]

Hi @Anonymous 

is the selection from a slicer? From which table/column?

[Anonymous]

Hi @tamerj1

yes it's from a slicer, i use the stringer code to turn the selection into a string adn then use that string for the search.

Steps_affiliate_only and the affiliate_only_2 (duplicate or the original table) are the tables used. Path_taken is the column the contains is looking through 

[tamerj1]

@Anonymous 

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

[Anonymous]

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

@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]

@tamerj1 

Works perfectly, thank you so much for your help!