Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculating mutual occurrences

So I have a dataset that contains cookie data, detailing a path taken by a customer until their purchase has been completed.

E.g.

 

Order id (Key)Pathtaken (String)
Order1website1 -> direct -> website2 -> direct
Order2website2 -> direct -> website2 -> direct
Order3website3 -> direct -> website2 -> direct
Order4website1 -> direct -> website3 -> direct
Order5website1 -> direct -> website1 -> direct
Order6website1 -> direct -> website2 -> direct

 

*With the above exampe, when website 1 and website 2 are selected the expected output would be 2, as website 1 and website 2 appear in 2 pathtaken together*

 

This path is broken up to different levels in the dataset, I am interested in level 4, which in the above example would be website1 and website2. I’m trying to count the number of times website1 and website2 appear together over all paths in the dataset.

I’ve duplicated the original table to make table2 and removed all columns apart from orderID and Level4 (for the name of the website). I’ve then used splicers to allow the user to choose 2 Level4 website addresses. I’ve then pulled that into a measure that turns the selection into a string (stringerT4 and Stringert4_second). This works fine when searching for the individual selections. But when I make the if statement to calculate the number of paths they BOTH appear in, it doesn’t quite work. I’ve gone for the contains function, but it just spits out one of the individual results and I can’t understand why.

 

I have the below code, the aim is to count the number of paths that contain both the selections:

 

Mutual = IF (

CONTAINS(Table, Table[path],Table[StringerT4])

&& (

CONTAINS(Table2, Table2[path],Table2[StringerT4_Second])) ,

COUNT(Table[order_id]))

 

The code for the stringer measures:

StringerT4 = CONVERT(SELECTEDVALUE(Table1[level 4]),STRING)

 

Is there an issue with what I’ve done that’s causing it to behave this way? I’m not sure if there’s a better function or way to achieve what I want.

TIA

2 Replies