Forum Discussion

jeyare's avatar
jeyare
Icon for Helper II rankHelper II
5 years ago
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 evalua...
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    Index W = 
MAXX(
    TOPN(
        1,
        FILTER( DATA, DATA[Age B] > EARLIER( DATA[Age B] ) ),
        DATA[Age A], ASC
    ),
    DATA[Weight A]
)

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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 ) )

 

Link to the sample pbix file