cancel
Showing results for
Did you mean:
Highlighted
Helper I

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

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!

5 REPLIES 5
Highlighted
Solution Sage

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

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

Highlighted
Super User IX

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

@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())

Proud to be a Super User!

Highlighted
Helper I

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

 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
Highlighted
Helper I

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

Thanks!

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

Highlighted
Solution Sage

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

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}}),

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021