Forum Discussion

davidap's avatar
davidap
Icon for Advocate I rankAdvocate I
2 years ago
Solved

DAX Retrieve value from another row and column within table based on matching criteria

Problem New Column Aggregate ID should return the Index ID where the User matches and Paid Date is between the First and Last Service dates of an Index ID. The Index ID marks a major event with Aggr...
Need Help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, davidap 

     

    You can try the following methods.

    Aggregate ID = 
CALCULATE ( MAX ( 'Table'[Index ID] ),
    FILTER (ALLEXCEPT( 'Table','Table'[User]),
        [Paid Date] >= 'Table'[First Service] 
            && [Paid Date] <= 'Table'[Last Service] 
    )
)

    Is this the result you expect? Please see the attached document.

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

Try this calculated column formula

Column = CALCULATE(MAX(Data[Index ID]),FILTER(Data,Data[User]=EARLIER(Data[User])&&Data[First Service  ]<=EARLIER(Data[Paid Date  ])&&Data[Last Service  ]>=EARLIER(Data[Paid Date  ])))

Hope this helps.