Forum Discussion
Solved
Offset + Match (Excel) equivalent in DAX
In a learning mode, can't find a solution for this example (simplified). Thank you for your tip.
to column "Index W":
- I need to MATCH the nearest greater value from "Weight B" for each evaluated row value of "Age A"
- then I need to write the value of "Weight A" matched
In Excel it is easy done by:
OFFSET(A1, MATCH(Age A, Age B , 1 ), 1)
Sample:
| Age A | Weight A | Age B | Index W |
| 1.0 | 10 | 1.1 | |
| 1.2 | 20 | 3.2 | |
| 3.6 | 30 | 3.7 |
Expected result:
| Age A | Weight A | Age B | Index W |
| 1.0 | 10 | 1.1 | 20 |
| 1.2 | 20 | 3.2 | 30 |
| 3.6 | 30 | 3.7 | blank |
Index W = MAXX( TOPN( 1, FILTER( DATA, DATA[Age B] > EARLIER( DATA[Age B] ) ), DATA[Age A], ASC ), DATA[Weight A] )
3 Replies
Hi,
The below is for creating a calculated column.
Index W CC =
VAR _ageB = Data[Age B]
VAR _condition =
MINX ( FILTER ( Data, Data[Age A] > _ageB ), Data[Age A] )
RETURN
CALCULATE ( SUM ( Data[Weight A] ), FILTER ( Data, Data[Age A] = _condition ) )Index W = MAXX( TOPN( 1, FILTER( DATA, DATA[Age B] > EARLIER( DATA[Age B] ) ), DATA[Age A], ASC ), DATA[Weight A] )Thx gents for the advice.
