Using rankx to get the first desired event

[CLQ]

Hello! I am learning by reading a Dax book (The Definitive Guide to DAX by Marcos Russo, I recommend it) and an exercise occurred to me.... which I could not carry out.

Let's pretend we have this table:

Name	Store	Datetime
John	TV	7/05/2022
Kely	TV	6/04/2022
Fred	Phone	3/03/2022
John	Phone	1/01/2022
Fred	TV	2/02/2022
Marcus	TV	5/07/2022
etc	etc	etc

Name-->it would be the ID, each Name is unique (it was easier for me with names than with numbers)

So, I want to create a column, called "First Purchase", which returns "Yes" if it was their first purchase and "No" otherwise. But, taking into account the Store column, since if you bought in a Store that sold "Phone" it is not valid, that is, if you buy first in a "Phone" and then in a "TV", First Purchase=Yes should appear in the row where the value "TV" is found, And if the user only purchased from Store=Phone, the result would still be "No"..

So, the table above would be:

Name	Store	Datetime	First Purchase
John	TV	7/05/2022	Yes
Kely	Phone	6/04/2022	No
Fred	Phone	3/03/2022	No
John	Phone	1/01/2022	No
Fred	TV	2/02/2022	Yes
Marcus	TV	5/07/2022	Yes

I was able to do it by going into Power Querry and removing the "Phone" values ​​from the table, but without removing anything, I can't get it to bring me the desired result.

I don't know if the Rankx function is the best, I suppose it is and that's why I put it in the title, but if there is another more comfortable or better way, I'm all ears 😁

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

First purchase = if(Data[Store]="Phone","No",if(CALCULATE(MIN(Data[Datetime]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Store]<>"Phone"))=Data[Datetime],"Yes","No"))

Hope this helps.

[CLQ]

It worked! Thanks. I was stuck in the logic of, using the rank, that the oldest date returns a 1 and then in another column I would do the "if". Yours is much more effective

 
Thanks

[Ashish_Mathur]

You are welcome.