Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
@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())
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 |
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |