Product Pathways

[SammyNed]

Good Morning Team,

Can someone please assist me?, I am looking for a way to show the pathways my customers are using to get a product.

Below Table 1 is a example of my data type, a list of customer ID's, date of order and product bought. 

Table 1:

Cst	Date	Product
A	2022/01/01	Kettle
A	2022/01/02	Hairdryer
B	2022/01/01	Kettle
C	2022/01/02	Toaster
A	2022/01/03	Kettle
D	2022/01/04	Toaster
C	2022/01/04	Kettle
B	2022/01/05	Hairdryer
D	2022/01/05	Kettle

Solution example:

I want to show the pathways my customers used to buy their lastest kettle. So looking only at their last kettle bought, 

Pathways to Kettle	Cst C & D bought a totaster before their last kettle	2	toaster -> kettle
	Cst A bought a hairdryer before their last kettle	1	hairdryer -> kettle
	Cst B bought a kettle first	1	kettle first

Is something like this possible in Powerbi?

Thank you in advance

Sam

[Anonymous · Accepted solution]

Hi @SammyNed ,

I have created a simple sample, please refer to it to see if it helps you.

Create a column.

rankx = RANKX(FILTER('Table','Table'[Cst]=EARLIER('Table'[Cst])),'Table'[Date],,ASC,Dense)

Then create a measure.

Kettle =
VAR _1 =
    CONTAINSSTRINGEXACT (
        CONCATENATEX ( ALLSELECTED ( 'Table' ), 'Table'[Product], "" ),
        "Kettle"
    )
        = TRUE ()
VAR _1first =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] )
                && 'Table'[rankx] = 1
        )
    )
VAR _2first =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] )
                && 'Table'[rankx] = 2
        )
    )
VAR _3first =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] )
                && 'Table'[rankx] = 3
        )
    )
RETURN
    _1first & "   " & _2first & "   " & _3first

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @SammyNed 

how many products do you have? What if two or more products are bought before the kettle?

[SammyNed]

Hi @tamerj1 

There are 5 products in total, however over 40k customers. I basically want to find some sort of corelation between the last product the customer bought and what they bought before that. So do more customers buy toasters and then kettles or do more customers buy haridryers first when they come on site for example.