Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
juli__sia123412
Frequent Visitor

Customers who bought one-time, first time and returned

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!

1 ACCEPTED SOLUTION

@juli__sia123412 ,

 

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"
    )

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

@juli__sia123412 ,

 

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"
    )

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

You're genius.

Thanks! It works perfectly!

Anonymous
Not applicable

I believe this can be accomplished through using a count function and group functionality. 

 

  1.  Create a calculated column
    1. Column = COUNTX(Table, CustomerID)
  2. From there you can create groups in the modeling tab based on the count to create 'bins' based on the quantity. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.