Using LookupValue to return the Max Value

[Anonymous]

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

[speedramps]

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

)

[VahidDM]

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

[AlexisOlson · Accepted solution]

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
    )