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?

Answer 1

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
)