finding the row with earliest date for each customerID who purchased specific product and return the date in new column

Question

I'm working with PowerBI and have the following table:

customer_id|item_id| date    
1          |   A   | 01/01/01        
1          |   B   | 01/01/01        
1          |   A   | 02/02/02        
1          |   A   | 03/03/03        
2          |   A   | 03/03/03        
2          |   C   | 03/03/03        
...

I would like to find the earliest date for each customer_id who purchased item A and return 1 in a new column. So that I get a new column in the table that looks like the following:

customer_id | item_id | date     | Column_want 
1           |   A     | 01/01/01 | 1
1           |   B     | 01/01/01 | blank
1           |   A     | 02/02/02 | blank
1           |   A     | 03/03/03 | blank
2           |   A     | 03/03/03 | 1
2           |   C     | 03/03/03 | blank
...

I've tried to filter the column by item A and then using TOPN(1,...) to choose only the top rows. However, it doesn't seem to work.

This seems like such a trivial request. Is there any smarter way around this?

score 0 · Accepted Answer · answered Feb 10 '20 at 17:42

It's possible to use TOPN for this but that function returns an entire row of a table so it looks pretty clunky like this:

Column_want = 
IF (
    Table1[item_id] = "A" && Table1[date]
        = SELECTCOLUMNS (
            TOPN (
                1,
                FILTER (
                    Table1,
                    Table1[item_id] = "A"
                        && Table1[customer_id] = EARLIER ( Table1[customer_id] )
                ),
                Table1[date], ASC
            ),
            "date", Table1[date]
        ),
    1
)

I'd suggest something more like this:

Column_Want = 
IF (
    Table1[date]
        = CALCULATE (
            MIN ( Table1[date] ),
            FILTER (
                ALLEXCEPT ( Table1, Table1[customer_id], Table1[item_id] ),
                Table1[item_id] = "A"
            )
        ),
    1
)

Or this:

Column_Want =
IF (
    Table1[date]
        = MINX (
            FILTER (
                Table1,
                EARLIER ( Table1[item_id] ) = "A"
                    && Table1[customer_id] = EARLIER ( Table1[customer_id] )
            ),
            Table1[date]
        ),
    1
)

score 0 · Answer 2 · answered Feb 10 '20 at 17:44

You could create a calculated column using variables:

    Column_want = 
            VAR Customer_id ='Table'[customer_id]
            VAR Earliest_date = CALCULATE(MIN('Table'[date]),
                                   FILTER('Table','Table'[customer_id]=Customer_id))
            VAR Earliest_item = CALCULATE(MIN('Table'[item_id]),
                                   FILTER('Table','Table'[date]=Earliest_date),
                                   FILTER('Table','Table'[customer_id]=Customer_id))
    RETURN IF('Table'[date]=Earliest_date && 'Table'[item_id]=Earliest_item,
               1,BLANK())

The idea is to calculate the earliest date for a particular Customer ID using Calculate and max (Earliest_date variable). Earliest_Item variable is calculated to avoid multiple records for the same customer getting tagged as 1. Hope this helps.

finding the row with earliest date for each customerID who purchased specific product and return the date in new column

2 Answers2