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:

AccountIDQuarterMapped Account Name
ABC IncID14320Q3 
ABC Inc.ID14320Q4 
ABC IncorpID14321Q1 
ABCID14321Q2 

 

My goal for the Mapped Account Name column is to return the account name associated with the latest quarter based on the ID.  As you can see these are all the same account, but the naming convention over the quarters has changed.  In this particular case, I want to return "ABC" because that is the name used in the most recent quarter, but my Lookupvalue isn't working correctly yet.

 

This is what I have:

Mapped Account Name =
var erm = 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)
 
It says a table of multitple values was supplied and errors out.
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
    )

     

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    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!!

     

  • speedramps's avatar
    speedramps
    Super User
    4 years ago
    Hi iab_pbi
     
    Try this measure
    it gets the last quarter for the ID
    and then get the account for the last quarter for the id
     

    Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !
     
     
    Map =
    VAR lastquarter =
    CALCULATE(
    MAX(Facts[Quarter]),
    ALLEXCEPT(Facts,Facts[ID])
    )

    RETURN
    CALCULATE(
    SELECTEDVALUE(Facts(Account]),
    ALLEXCEPT(Facts,Facts[ID]),
    Facts[Quarter] = lastquarter
    )