Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |