topic Re: Customers who bought the same product from a different distributor in a different channel in DAX Commands and Tips

Customers who bought the same product from a different distributor in a different channel

markjohnson952 — Thu, 01 Oct 2020 20:55:58 GMT

Hello,

I am trying to wrap my head around creating a DAX expression that can tell me customers who earlier bought a product from a non-digital distribution channel but then at a later date bought that same product from a digital distributor. I am hoping for the expression to indicate the First Buy of that product and then the Repeat buy as my results.

Below is an example of my data set with my desired outcome in the Result column.

Thanks!

Re: Customers who bought the same product from a different distributor in a different channel

CNENFRNL — Thu, 01 Oct 2020 23:27:36 GMT

Hi, markjohnson952 , Surely both Power Query and DAX do the trick; but you'd better paste your dataset so that others don't need to spend too much time typing all data.

cheers

Re: Customers who bought the same product from a different distributor in a different channel

amitchandak — Fri, 02 Oct 2020 07:19:35 GMT

markjohnson952 , Create these columns. The first two can be variable inside the third one

Min Date CT= minx(filter(table,[customer] =earlier([customer])),[Date Sold])

Min Date CT DC = minx(filter(table,[customer] =earlier([customer]) && [Distribution Channel] =earlier([Distribution Channel])),[Date Sold])

flag = Switch(true() ,
[Date Sold] =[Min Date CT] , "First Buy",
[Date Sold] =[Min Date CT DC ] , "Repeat",
blank())

Re: Customers who bought the same product from a different distributor in a different channel

markjohnson952 — Fri, 02 Oct 2020 11:54:41 GMT

Customer	Date Sold	Product	Distribution Channel	Distributor	Result
Don	1/7/2020	Bread	Non-Digital	Cub	First Buy
Don	1/10/2020	Bread	Non-Digital	Cub
Don	2/20/2020	Bread	Digital	Rainbow	Repeat
Don	8/1/2020	Bread	Non-Digital	Lunds
Don	9/3/2020	Bread	Digital	Rainbow
Don	9/10/2020	Bread	Digital	Rainbow
Don	10/1/2020	Bread	Digital	Rainbow
Greta	4/5/2020	Pens	Non-Digital	OfficeMax
Greta	1/20/2020	Pens	Digital	Costco
Jack	3/3/2020	Soap	Non-Digital	TJ Maxx
Jack	2/10/2020	Soap	Digital	REI
Mark	5/28/2020	Pillows	Non-Digital	Homegoods	First Buy
Mark	6/2/2020	Pillows	Non-Digital	Pier 1
Mark	7/7/2020	Pillows	Digital	Kohls	Repeat
Jim	10/1/2020	Beer	Digital	Total Wine
Jim	9/20/2020	Beer	Digital	Haskells

Re: Customers who bought the same product from a different distributor in a different channel

markjohnson952 — Fri, 02 Oct 2020 13:22:32 GMT

amitchandak

Thanks!

Is it possible to use only a measure to solve this problem?

Re: Customers who bought the same product from a different distributor in a different channel

CNENFRNL — Fri, 02 Oct 2020 13:28:55 GMT

Hi, markjohnson952 , as mentioned before, a PQ solution is available. Pls refer to the code below (not very elegant to be honest😂),

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZJNTwIxEIb/ymbPmG6LCntVjLiKEiXxQDiUZcCG0jHdJfDzHXClwy7BcOpHnqczb9vxOO6hi1uxFB2hEpXQ9M6DntH4iu6qZxam1JZW9+tpPGkFXCaX8IrgOh/Yd23cFDeM7wp5/viXtZsVTEhF+5Lz0xP9n+OJbjR0mn/0UGrauxY3f8IQXNEI8DafmxwGentkSXZPlcauFIsyxz2f6XxJO+2Q+gP1d6PIKIuowpYriiWvHBbk4WnPDrTfsZSge+jGWIubZo4+rmCBWD1GJd4K9Z83NOAjyaVO+IJBCsIzftnfIplZ1Z8EwB/BI6Qx+jQOmJHyT1g3+rpYgt1VmPwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Date Sold" = _t, Product = _t, #"Distribution Channel" = _t, Distributor = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Date Sold", type date}, {"Product", type text}, {"Distribution Channel", type text}, {"Distributor", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [ achats = Table.Group(#"Changed Type", {"Customer", "Product"}, {{"All", each _}}){[Customer = [Customer], Product = [Product]]}[All], #"non-digital" = Table.SelectRows(achats, each [Distribution Channel] = "Non-Digital"), #"first non-digital" = List.Min(#"non-digital"[Date Sold]), digital = Table.SelectRows(achats, each [Distribution Channel] = "Digital" and [Date Sold] >= #"first non-digital"), #"first digital" = List.Min(#"digital"[Date Sold])??#date(1,1,1), result = if [Distribution Channel] = "Non-Digital" then if [Date Sold] = #"first non-digital" and #"first non-digital" <= #"first digital" then "First Buy" else "" else if [Date Sold] = #"first digital" then "Repeat" else "" ][result] ) in #"Added Custom"