What are the next categories in the next purchase? (Making a measure)

[Alaintjes]

Hello, 

I hope you guys can help me out with a simple question. 

Its all about repeatpurchases and the question is: 

I want to measure which categories the next purchase has based on Account_id where Rank (Which stands for Purchase) = -1

Outcome: you can see which categories (text) all the customer bought in the next order. (and because of my DAX formula i can see within howlong this is. 

If i place the measure into my visualisation i want to see all the categories with rank plus 1. 

Under an example of the visualisation i want to make. 

Case explained:

I am analyzing data for a company that sells shoes. Many products within this company have different categories.

When a customer places an order, the order can exists of more than one product. so let's say 3 products. 

But when a customer returns within a while (lets say 6 months) he places a new order with (for example) 4 products. 

I want to analyse al the different ways this category path can have for order 1 to order 2 ( 3x4 different ways = 12 ways). 

and of course for order 2 to order 3 and order 3 to order 4 and so on... 

In the picture above i have filtered just 1 account so you can see what i mean. 

I have ranked the orders for each unique accountid. (So rank 1,2,3,4,5 ect.) Sometimes you have the same rank (for example 1) 

this means an order have more products (in this case the order has 2 products)

Hope you guys have a simple solution how to measure the question: 

I want to measure which categories the next purchase has based on Account_id where Rank = -1

I made a small CSV. file which i put on my google drive so you guys can try some things out in Power BI. this CSV contains 3 account's with multiple purchases. 

hope you can find the simple solution. 🙏

https://drive.google.com/file/d/1vZ8TfCC1p5CxfuxaRDhjX1wA7RKHZbjW/view?usp=sharing 

Thanks in advance!

Greetings Linda

[lbendlin]

Access denied on the Drive file.

[Alaintjes]

Thanks for answering, it should be working now! 

https://drive.google.com/file/d/1vZ8TfCC1p5CxfuxaRDhjX1wA7RKHZbjW/view?usp=sharing 

[lbendlin]

So to recap - for each account you want to look at adjacent transaction dates, and get the pairing of what they bought on the former date and what they bought on the latter date?  Presumably then displaying this in a Sankey ? With the time in between purchases as a weight?  

Here's a possible approach using inline table expansion  (to avoid having to do fancy joinery)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZltb9pIEMe/itXXh7KzD35gXxmalFb0Kh2J8qLXF4a6AQVwztin6336zvoxGNtrwrZpJBQMyL+Z3Zn/zKw/f35DCEBM8A/+gZkz9/07ScmIjSihYFE2pkSCvI924f5qsY7Cw9U0OKTB1souJLiSSe564Mm/U0LYyrKtXbRP1ofimpWXi6d4s3+QH4N4tX7z5Y9WsJNziUVgTBT3XbT9dpXDZ9+X8eartJFHCdik5EEOeAmOjUTuJtG66UkhbfoMq3Xze4P69P/yW+T7vqRQYd0xb2JvtkFysNRNqW1Ngu02xPsFaAFFC5zBXv/+dOd0yTvpBOn8xfQ6rsUIIA8w8MYcGvjZ5hFvUPJv42CzlcAlgLS5cEG38zdosPwz+jfcLcP4bBMmwUPlur9ahYdDFG/QqNsoCasvZsH+6xJ/aN4sMgKKZoFXpZ5uZZgKCga2q9uX+80+CWP5Nlz1m0BzE9TKHMVG18pMgtXjE75wNWxlC3BKTdmCy3EsRC/ZJNwjybnNtZpRWPUh2KdB3B29uDqXGyUkldwRdKhRN+Ey7rXKHrmFVUd6cmY8E+lKl3meXl/T3Q7N8tOH9JB0GuWM7CKU3LE4J5RcLDBAPAJDDfmQ7sMuMzDTgeR2HBfSLjum2zRZrVW9YZnogfDEoPz+tEqinni2R16xR2iG0Jpxr6S3VuL7eHNI1AcqyzxZKg4j1m6zT3E7Tz/IjFqET0lbmrVVA/u0Fr2N0aCi/qryW4VsH1hfgk56jC4mUxLHvGcC19gCe1iv4f3n+9eSernwP1PYjxV2EkW4ulRp+iDB6NP0ylFGquhDomgjChVlQLUS3hNj9WZ6NY6PWRuOKxzjhKAxpWtWFOO7OCyQ3rCwLta0VGnMctrhHyK1nYNOf1+VVkbLp1Tl4NU8DJJ1GJe5qfjWNMD1M11tvFJCCcmDx7+dza9v30+t+fX7xd1f19Z0fjfBzETBdIm2OdOrZUsP0o401Hm0t8SiKQkLrFHBNpPkLF1MaFB/G1wRL2t7X4mGIlDQgHbTnEzQibC75sZTcT27BTnxz2iL8SrQjr7mGGq0h+k/C6j8NHoMQN2TWtkGzeolUNvWT+WDhqCjLol3eQqqBRICfzCgZp7TAjnNotk6j8LFKtTtrxZt1PP+Tc5LKijq0H5T1xD9MqJ+pj0iGppeaz9rKhFj1jzmyKkZ1LEF1x4gDKRC7wlC1eHi9MkuH9M7e+qjlN2HwWMYm2qshx4jVVjThzSQ5atylvY4q7L00glNP8E2oUbm1fbBsJvqKi0UXNsH6qUQ2IgXGdO3tg522YIxfRwVRB//bXs6/J6EqZjMVM4UntbiQEnfjgrFpVzUJf1CsMiPr0ANFKQ7aRxMV8cFe6guaQ+oOs7yGlzT41N/p1ZH8OWt2rCBv0IamPm7FamFZ1aPfhGwTYm8Zn/2XOiNPJ84TZHi6ZPpnDg5A8s5Zp6t1bWZHMtb4YyKv6H7c278lYifGXI/gdHSmeaUl484X34A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID;Order Date;Total Orders;Category;rank;Month;Time difference;time between;Montly time difference;Season;Month Txt" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Demoted Headers", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"Order Date", type datetime}}, "de-DE"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Total Orders", Int64.Type}, {"rank", Int64.Type}, {"Time difference", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Prev Order", each Table.SelectRows(#"Changed Type",(k)=>[Account ID]=k[Account ID] and [rank]=k[rank]+1)),
    #"Expanded Prev Order" = Table.ExpandTableColumn(#"Added Custom", "Prev Order", {"Order Date", "Category"}, {"Prev Order Date", "Prev Category"})
in
    #"Expanded Prev Order"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Alaintjes]

Hi Ibendlin, 

I want to thank you for your big effort about this case. It come close to what i really need. You've created a new collumn with just one outcome in previous category. But this is actually not good. Because sometimes you have more options in previous category, not just only one. But within a column you cannot insert multiple previous categories. With a measure you can i believe! 

I will explain it one more time very shortly:

The outcome must be a measure: that measures the next categories based on account id! and that categories must be placed in visualization number 2. 

So when i press in visualization number 1 some random category, the measure will detect in visualisation number 2 which categories are bought next (this can be 1,2 or 3 ect categories, not just only one) 

The outcome will be all the next categories in general. Not for just only one or 2 accounts. But all the accounts together, and always the next purchase. so from 1 to 2, 2 to 3, 3 to 4 and so on... (thats why i explained in the measure, based ond account_ID and always rank +1 ) 

I really hope you can look in to it once more, it will be really appreciated! 

Greetings Linda. 

[v-yingjl · Accepted solution]

Hi @Alaintjes ,

Continue with @ lbendlin's step in Power Query, Power Query looks like a better way to achieve this thread that you can try the whole query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1Zlbc5tGFMe/CqNnM94rF70hxa6SUZqZyh4/pHlACrE0RuAi6DT99N0FdoWkhV0USFzPIGGE4MfZc/7nos+fJwBAmAH2B/+CC3cZBI+TmwkCNrYRQNBCeIoAOwLZ9pTuo+R2tU2jw+08PBRhbJX/8I899oLZRjwf+uz9zwIAvLEca58m+fYgj+DjgdVrtkue2c7HMNtsJ19uWmDcigVYAE6BYPktjb/dVkCL7+ts95Udc2oGBKADjgywuuWPIGCbVuYAhubgJqAcCZ2gGJrj+xnJ67/rb2kQBPzZoETxpkSFch+H+cHil0eONQvjOGLXDUsqVFO5va3zfyVy1cvVQQRqIvLDRM14ojaElRNDf0qgAmmxe2EXEkwPWbiL+VmEv/BzHUI9aOZJ9+yB2Nvv6d/Rfh1lV2LNwmdpomCziQ6HNNsx0Ic0j+QHizD5umYnjo8KbIgYKvRPZEBvQSzcDEPHM1vTp12SM5abybtoo8NCFRa34IWvtVlwFm5eXtlWWs0RfJAgNDwfM9uleF63wKT+NiEOMdQ0SfohTIow64oQZsWhQHn4cosSl6K+oPfROtOQOrZXk17oXe+Y4V/nqdPDvm+aJ4r9vkQNiufikHeAurZTu6Y3pf1d06tTKgQ+gH3hPhRJ1I7GVAeCiu2ywGhjm8dFvtlW+RVLqYbUp7205tMmTztjxrH9en0ZGjVCe+Ip5JhRnrLdIecHRGz79VVqJmDtd0mRRwf1oRp0Fb3mqvBWZzpHnXvfZQxS1iWiLGmEhQ7GLOUqa7R2DiyEGfsnonyxfI55reb/EwR3/Nn8KqmdZYqPEmWWpuXKIJGveghad75qGAQD6eGMgrZTUOHJEBmmp04/bjqHf0QgU9yOQAQCJgCUqMIEVpqxvSySGL55OMn1EBmIqRDS2KEEMay89LnlDRI0PfJTwTXidhmF+TbKhHZwJmseljYfN+P6Ij0AcHTQ4GGxvHt4P7eWd+9Xj3/cWfPl46xWjjIZeMCwMDbLBC11XRvGCNVcWytDVTK2Yrk7jGUKksE7pJbq25cGxXDtypsiYMJVE0CkI3AnIqkB6nTNHtTJ5MpST2mH0Uq5NwPSUVOeg4xYP+rnUw17jDiaQp6y1lCDyHoDIscxnQoZN8gnVSvptgisBR1SWp5qXHP0K0ldVdHRMueAg6qpzi5GOKNZSO80oiSBgiUpyqv36h/0ReovJjGdo5yRjDAxadrkSALoFKtGdYJEgrgOJYYDrx4kUDvxOulkyokHHm6EpOmnTqQlicKXKBunqeo3Rm2gjDuQhKWucKMgrVGEmlwTO22CYjpNuYQZfHbSNpDQkXhC8ykxrOXNJB9im9QRrF8bXj1yR6EYm/qrpAjYTtzZ9WkCuMEhO5shY1ha5ChqCOg9hAoWRGizZBoIhlajXsgbT6ALYrcWFdeDTl+NNRjmdszHL1jGbcf1lfVp5AxZWvcZXDUwBp5d6dRVyTC2tv5SELWy+qq6+jzxDf4bpjps5S/e48apcr4s7j387/7N2gdcSrh8aOH/16z5dTHQvPXPdvufdO+WrkTcfZg2+st/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, #"Order Date" = _t, #"Total Orders" = _t, Category = _t, rank = _t, Month = _t, #"Time difference" = _t, #"time between" = _t, #"Montly time difference" = _t, Season = _t, #"Month Txt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID", type text}, {"Order Date", type text}, {"Total Orders", Int64.Type}, {"Category", type text}, {"rank", Int64.Type}, {"Month", Int64.Type}, {"Time difference", Int64.Type}, {"time between", type text}, {"Montly time difference", type text}, {"Season", type text}, {"Month Txt", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Order Date", type datetime}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Prev Order", each Table.SelectRows(#"Changed Type",(k)=>[Account ID]=k[Account ID] and [rank]=k[rank]+1)),
    #"Expanded Prev Order" = Table.ExpandTableColumn(#"Added Custom", "Prev Order", {"Category"}, {"Prev Category"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Prev Order", "Category", "Category - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Category", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Category"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each Text.PositionOf([Prev Category],[Category]) >= 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = true)),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Prev Category", "Category - Copy"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Account ID", "Order Date", "Total Orders", "rank", "Month", "Time difference", "time between", "Montly time difference", "Season", "Month Txt", "Merged"}, {{"Sum", each Text.Combine([Category],"/"), type nullable text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Grouped Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Pre Category", "Category"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Pre Category", type text}, {"Category", type text}}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"Sum", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sum"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Sum", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type3", {"Account ID", "Order Date", "Total Orders", "rank", "Month", "Time difference", "time between", "Montly time difference", "Season", "Month Txt", "Pre Category", "Category"}, {{"Distinct", each Text.Combine(List.Distinct([Sum]),"/"), type nullable text}})
in
    #"Grouped Rows1"

The [Distinct] column would be the result column that you may need in this thread.

Simliar thread that you can refer: Comparing String in two columns for differences 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

"But within a column you cannot insert multiple previous categories"

That is incorrect. I did exactly that in Power Query. Each table cell can contain child tables or lists or records. But at some point you need to flatten these out, and that is what I did with this step

 #"Expanded Prev Order" = Table.ExpandTableColumn(#"Added Custom", "Prev Order", {"Order Date", "Category"}, {"Prev Order Date", "Prev Category"})

Which creates the desired cartesian product.

"I want to measure which categories the next purchase has based on Account_id "

"Not for just only one or 2 accounts. But all the accounts together, and always the next purchase"

I would say that these requirements contradict each other. Please clarify.