Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
Hope someone could help me with this case.
I have table
OrderID - CustomerID - SKU - Purchase date
( in case of two or more sku were bought, it will be few corresponding records with the same orderID & date)
I want to create a measure and categorize my orders to:
- One-time ( bought once and never back)
- First ( first order in case customer bougth again any time later)
- Repeated ( next orders )
Thank in advance!
Solved! Go to Solution.
You may try the calculated column below.
Column = VAR r = RANKX ( FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) ), Table1[OrderDate], , ASC, SKIP ) + RANKX ( FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) && Table1[OrderDate] = EARLIER ( Table1[OrderDate] ) ), Table1[OrderID], , ASC, SKIP ) - 1 RETURN SWITCH ( TRUE (), ISEMPTY ( FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) && ( Table1[OrderDate] <> EARLIER ( Table1[OrderDate] ) || Table1[OrderID] <> EARLIER ( Table1[OrderID]) ) ) ), "One-time", r = 1, "FirstOrder", "Returned" )
Hi,
Share some data and show the expected result.
For example -
OrderID - Customer ID - SKU - OrderDate -TYPE
123 - 456 - SKU1 - 1/1/2019 - FirstOrder
123 - 456 - SKU2 - 1/1/2019 - FirstOrder
156 - 267 - SKU 2 - 1/1/2019 - One-time
1245 - 456 - SKU3 - 04/2/2019 - Returned
167 - 456 - SKU1 - 9/4/2019 - Returned
You may try the calculated column below.
Column = VAR r = RANKX ( FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) ), Table1[OrderDate], , ASC, SKIP ) + RANKX ( FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) && Table1[OrderDate] = EARLIER ( Table1[OrderDate] ) ), Table1[OrderID], , ASC, SKIP ) - 1 RETURN SWITCH ( TRUE (), ISEMPTY ( FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) && ( Table1[OrderDate] <> EARLIER ( Table1[OrderDate] ) || Table1[OrderID] <> EARLIER ( Table1[OrderID]) ) ) ), "One-time", r = 1, "FirstOrder", "Returned" )
You're genius.
Thanks! It works perfectly!
I believe this can be accomplished through using a count function and group functionality.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |