Forum Discussion
Populate user rows with store name from first purchase
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 |
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])
3 Replies
- amitchandak
Super User
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])This worked. Thank you for the solution. Danced around this the whole day 🙂
- selimovd
Most Valuable Professional
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
