Return value from another column

[kfarynowski]

I'm using lastnonblank to find a value in a table (all case statuses), but want to bring back the value in a different column in that same row.   The formula is

Year End Status = CALCULATE(

   MAX('All Case Statuses'[StatusID]),

   FILTER(FILTER('All Case Statuses','All Case Statuses'[CaseStatusYear] <= 'Base Case Expanded'[YearEndStatusYear]),

   LASTNONBLANK('All Case Statuses'[StatusID],MAX('All Case Statuses'[StatusID]))))

What I want to return is the value in the "all case statuses" table for another column instead of the "All Case Statuses[StatusID].  This formula brings back the correct value for "StatusID", but I want the value for "Description" which is in the same (All Case Statuses) table in that same row.  

These two table (All Case Statuses) and (Base Case Expanded) are both related (many/one) to a (Base Case Data) table that has one entry per case using a caseID.

[Greg_Deckler · Accepted solution]

Perhaps LOOKUPVALUE?

https://msdn.microsoft.com/en-us/library/gg492170.aspx

[kfarynowski]

That worked, but I need to return two values (from separate columns).   If I try to do two columns using the lookup function, I get an error indicating circular dependency.

The formula for the current calculated column that does the lookup is:

Year End Status = LOOKUPVALUE('All Case Statuses'[CaseStatusDescription]
    ,'All Case Statuses'[StatusID],(CALCULATE(
    MAX('All Case Statuses'[StatusID]),
    FILTER(FILTER('All Case Statuses','All Case Statuses'[CaseStatusYear] <= 'Base Case Expanded'[YearEnd]),
    LASTNONBLANK('All Case Statuses'[StatusID],MAX('All Case Statuses'[StatusID])))))

This brings back the Case Status Description from the All Case Statuses table, but I also want to bring back another value (and upt in a differect column) based on the same search value.