Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using LookupValue to return the Max Value

This is what my data looks like: Account ID Quarter Mapped Account Name ABC Inc ID143 20Q3   ABC Inc. ID143 20Q4   ABC Incorp ID143 21Q1   ABC ID143 21Q2     My go...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    This works as a calculated column. If you want to use it as a measure, then you need an aggregating function for Account_Data_Total[ERM ID] in your definition of VAR erm.

     

    Try this:

    Mapped Account Name =
VAR erm = SELECTEDVALUE ( Account_Data_Total[ERM ID] )
VAR maxqtr =
    CALCULATE (
        MAXX ( Account_Data_Total, Account_Data_Total[Quarter] ),
        FILTER ( Account_Data_Total, Account_Data_Total[ERM ID] = erm )
    )
RETURN
    LOOKUPVALUE (
        Account_Data_Total[Account],
        Account_Data_Total[ERM ID], erm,
        Account_Data_Total[Quarter], maxqtr
    )

     

VahidDM's avatar
VahidDM
Super User
4 years ago

HI Anonymous 

Try this code to add a new column to your table:

Mapped Account Name =
CALCULATE (
    MAX ( [Account] ),
    FILTER (
        'Table',
        'Table'[ID] = EARLIER ( 'Table'[ID] )
            && 'Table'[Quarter]
                = CALCULATE ( MAX ( 'Table'[Quarter] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
    )
)

 

Output:

 

 

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

Appreciate your Kudos!!