cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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.

 

markjohnson952_0-1601585674560.png

 

Thanks!

 

 

 

5 REPLIES 5
Highlighted
Solution Sage
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
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())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

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

CustomerDate SoldProductDistribution ChannelDistributorResult
Don1/7/2020BreadNon-DigitalCubFirst Buy
Don1/10/2020BreadNon-DigitalCub 
Don2/20/2020BreadDigitalRainbowRepeat
Don8/1/2020BreadNon-DigitalLunds 
Don9/3/2020BreadDigitalRainbow 
Don9/10/2020BreadDigitalRainbow 
Don10/1/2020BreadDigitalRainbow 
Greta4/5/2020PensNon-DigitalOfficeMax 
Greta1/20/2020PensDigitalCostco 
Jack3/3/2020SoapNon-DigitalTJ Maxx 
Jack2/10/2020SoapDigitalREI 
Mark5/28/2020PillowsNon-DigitalHomegoodsFirst Buy
Mark6/2/2020PillowsNon-DigitalPier 1 
Mark7/7/2020PillowsDigitalKohlsRepeat
Jim10/1/2020BeerDigitalTotal Wine 
Jim9/20/2020BeerDigitalHaskells 
Highlighted
Helper I
Helper I

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

@amitchandak 

Thanks!

 

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

Highlighted
Solution Sage
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}}),
    
    #"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"

 Screenshot 2020-10-02 152818.png

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors