Populate user rows with store name from first purchase

[MikaelB]

Hi experts.

Problem:
Trying to create calculated column that looks up which store user used in their first purchase and populate column with that data.

Wanted outcome:

USER_ID	PURCHASE_DATE	STORE_NAME	FIRST_STORE_NAME (wanted outcome)
1	1.1.2020	A	A
1	2.1.2020	B	A
1	3.1.2020	B	A
2	1.1.2020	C	C
2	2.1.2020	B	C
2	3.1.2020	C	C
3	1.1.2020	A	A
3	2.1.2020	A	A
3	3.1.2020	C	A

[selimovd]

Hey @MikaelB ,

you should get the result with the following calculated column:

FIRST_STORE_NAME =
VAR vRowUser = myTable[USER_ID]
VAR vFirstPurchase =
    CALCULATE(
        MIN( myTable[PURCHASE_DATE] ),
        ALLEXCEPT(
            myTable,
            myTable[USER_ID]
        )
    )
RETURN
    CALCULATE(
        MIN( myTable[STORE_NAME] ),
        myTable[USER_ID] = vRowUser,
        myTable[PURCHASE_DATE] = vFirstPurchase,
        ALL( myTable )
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[amitchandak · Accepted solution]

@MikaelB , Create a new column like

New column =
var _max = Minx(filter(Table, [USER_ID] = earlier([USER_ID])),[PURCHASE_DATE])
return
[Value] -maxx(filter(Table, [PURCHASE_DATE] = _max && [USER_ID] = earlier([USER_ID])),[STORE_NAME])

[MikaelB]

This worked. Thank you for the solution. Danced around this the whole day 🙂