PowerBI DAX - Identifying first instance based on multiple criteria

0
votes

Using DAX to identify first instance of a record

I'm faced with trying to identify the first instance in a database where someone (identified by the ID column) has purchased a product for the first time. It's possible for said person to purchase the product multiple times on different days, or purchase different products on the same day. I drummed up an excel formula that gets me there, but am having trouble translating into DAX.

=COUNTIFS(ID,ID,PurchaseDate,"<="&PurchaseDate,Product,Product)

Which results in the correct values in the "First Instance?" Column.

enter image description here

Ideally I won't have to hardcode values, as I would like to use the "Product" column as a parameter in the future. If there are other suggests aside from translating this in DAX, that would also be appreciated! (IE using filters, or other tools in PowerBI)

Thanks in advance!

1
powerbidaxcountif
Do you want it as a calculated column or a measure? – RADO
From what I read, a calculated column would be best (since the results are stored in the table, and not calculated at the time of rendering like measures are). I'm fairly new to PowerBI, so any other thoughts are encouraged! – Tyler
I'd recommend to read this first: sqlbi.com/articles/calculated-columns-and-measures-in-dax – RADO
I appreciate it! Thanks RADO – Tyler
@RADO This was a great read, and also a great resource of other information. Thanks again for the suggestion! – Tyler

1 Answers

1
votes

This is very similar to an answer I gave to another question (which you can find here).

In that question, the request was to see a running count of rows for the given row's criteria (product, year, etc.). We can modify that slightly to get it to work in your problem.

This is the formula I provided in the answer I linked above. The basic concept is to use the EARLIER functions to get the value from the row and pass it into the filter statement.

Running Count = 
    COUNTROWS(
        FILTER(
            'Data',
            [ProductName] = EARLIER([ProductName]) &&
            [Customer] = EARLIER([Customer]) &&
            [Seller] = EARLIER([Seller]) &&
            [Year] <= EARLIER([Year])
        )
    )

What I would suggest for your problem is to create this as a TRUE/FALSE flag by simply checking if the running count is 1. This formula will evaluate to a Boolean flag.

First Instance = 
    COUNTROWS(
        FILTER(
            'Data',
            [ID] = EARLIER([ID]) &&
            [Product] = EARLIER([Product]) &&
            [Purchase Date] <= EARLIER([Purchase Date])
        )
    ) = 1