Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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)
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)
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |