cancel
Showing results for
Did you mean:
Member

## Look up value based on two criteria?

So I have 3 columns involved in this issue. [Name], [Date], and [ID]. The [ID] column is unique but can contain multiple [Name] values for each [ID]. What I want to return is the most recent [Name] based on the [ID] using the [Date] column. How would I go about doing this?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Look up value based on two criteria?

@nobodyukno try followin measure

```Recent Name =
VAR __latestDate =
CALCULATE(
MAX( Table[Date]),
ALLEXCEPT( Table, Table[Id])
)
RETURN
CALCULATE(
MAX( Table[Name]),
Table[Date] = __latestDate
)```

Add a table visual, put Id and Recent Name measure on and you will have the result

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

5 REPLIES 5
Super User

## Re: Look up value based on two criteria?

@nobodyukno try followin measure

```Recent Name =
VAR __latestDate =
CALCULATE(
MAX( Table[Date]),
ALLEXCEPT( Table, Table[Id])
)
RETURN
CALCULATE(
MAX( Table[Name]),
Table[Date] = __latestDate
)```

Add a table visual, put Id and Recent Name measure on and you will have the result

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Member

## Re: Look up value based on two criteria?

Thank you @parry2k. Is there a way to do this but for a calculated column instead?

Member

## Re: Look up value based on two criteria?

Any other ideas? I need a calculated column as I need to use a matrix instead of a table.

Community Support Team

## Re: Look up value based on two criteria?

As tested, parry2k's solution can be used in a calcuated column or a measure

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

## Re: Look up value based on two criteria?

Hi,

This can be done in a calculated column but then that column would not refresh when you make a change in a filter/slicer.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 221 members 2,043 guests
Recent signins: