Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |