topic Re: IF with multiple Contains in DAX Commands and Tips

IF with multiple Contains

Anonymous — Wed, 25 Jan 2023 15:44:22 GMT

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.

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

Re: IF with multiple Contains

tamerj1 — Wed, 25 Jan 2023 17:11:29 GMT

Hi Anonymous

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

Re: IF with multiple Contains

Anonymous — Wed, 25 Jan 2023 17:27:24 GMT

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

Re: IF with multiple Contains

tamerj1 — Wed, 25 Jan 2023 18:10:15 GMT

Anonymous

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

Re: IF with multiple Contains

Anonymous — Thu, 26 Jan 2023 10:05:43 GMT

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.

Re: IF with multiple Contains

tamerj1 — Thu, 26 Jan 2023 11:34:20 GMT

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

Re: IF with multiple Contains

Anonymous — Thu, 26 Jan 2023 12:40:55 GMT

tamerj1

Works perfectly, thank you so much for your help!