How to return the first value of a second column based on a first column values

[Esott]

Hello everybody, 

Im new to Dax world so I kindly ask for your help.

i am stuck on this problem, where I would like to build a calculate column where, for every distinct value on a first column, i want to return  the first identifiable value on the second column and this value must be equal for every row of the calculated column till the value on the first change.

Here's an example:

Code	Product	Calculate Column
1234	chicken	chicken
1234	chicken	chicken
1234	chicken 2	chicken
1235	sandwich 1	sandwich 1
1235	sandwich	sandwich 1
1235	sandwich	sandwich 1
1236	soup	soup
1236	soup	soup
1236	soup 2	soup

As you can see, id like that for the code 1234 the calculated column will return the first value finded on the column product, which is "Chicken" (yes i'm hungry right now 🙂)

Anybody has any idea or suggestion?

Really appreciate your help and thank you in advance,

E

[lbendlin · Accepted solution]

DAX has no concepts of column numbers or row numbers. Augment your sample data with an index column to allow DAX to understand the order of the rows.

CodeProductIndex

1234	chicken	1
1234	chicken	2
1234	chicken 2	3
1235	sandwich 1	4
1235	sandwich	5
1235	sandwich	6
1236	soup	7
1236	soup	8
1236	soup 2	9

Once the index is in place the DAX formula becomes trivial.

Column = 
var i = CALCULATE(min('Table'[Index]),ALLEXCEPT('Table','Table'[Code ]))
return CALCULATE(min('Table'[Product]),ALLEXCEPT('Table','Table'[Code ]),'Table'[Index]=i)