Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power BI gurus,
I need to write a DAX statement that can order what number a customer's purchase was according to when the purchase was made:
Order Table
Customer ID | Product ID | Date |
C41 | PR488 | 15/01/2015 |
C03 | PR100 | 24/10/2018 |
C41 | PR100 | 2/07/2015 |
C41 | PR100 | 1/09/2014 |
C86 | PR156 | 18/2/2020 |
C03 | PR100 | 25/10/2018 |
C17 | PR488 | 2/2/2019 |
C03 | PR488 | 23/11/2018 |
Note there is no Primary Key in this table.
In other words, I need another column that says what number the purchase was for that particular customer according to the date of the purchase.
How could this problem be solved using a calculated column in DAX?
Thanks in advance.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Expected result CC =
RANKX (
FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
Data[Date],
,
ASC
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Expected result CC =
RANKX (
FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
Data[Date],
,
ASC
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I would do this in the query editor by first doing a Group By step on your Customer column and keeping all rows, then add a rank column to those grouped tables with Table.AddRankColumn, and then re-expand all but the Customer column.
Here is an example. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67DcAgDIThXaiRfOdAcOosEKVF7L9GeBUgpbri0y87Z3cHOu+eN5jVZRRQFIyu+Io4OhKoq0GIhjZwlhMFaQk3o+BqFobZOSy2pYlWU/wdjPtBpuVV7R2vrZt0CDm78gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Product ID" = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Product ID", type text}, {"Date", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
#"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Customer ID"}, {{"AllRows", each _, type table [Customer ID=nullable text, Product ID=nullable text, Date=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Ranked", each Table.AddRankColumn([AllRows], "OrderNumber", {"Date", Order.Ascending})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
#"Expanded Ranked" = Table.ExpandTableColumn(#"Removed Columns", "Ranked", {"Product ID", "Date", "OrderNumber"}, {"Product ID", "Date", "OrderNumber"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Ranked",{{"Product ID", type text}, {"Date", type date}, {"OrderNumber", Int64.Type}})
in
#"Changed Type1"
If you really need to do it with a DAX column, you could use this column expression (replace OrderRanks with your actual table name):
OrderNumberDAX =
VAR thisdate = OrderRanks[Date]
RETURN
CALCULATE (
COUNTROWS ( OrderRanks ),
ALLEXCEPT ( OrderRanks, OrderRanks[Customer ID] ),
OrderRanks[Date] <= thisdate
)
Pat
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |