cancel
Showing results for
Did you mean:
Highlighted
Post Partisan

VLOOKUP with MAX

Hello everyone!

I have two tables:

tblOrder

 Order Date Value 1001 01.01.2020 500 1002 01.01.2020

tblWork

 Order SEQ State String 1001 50 1 AAA 1001 50 2 BBB

Now I would like to add the first table a calculated column that shows me the String for the row of tblWork where SEQ & State = MAX

How would you do that?

3 REPLIES 3
Highlighted
Anonymous
Not applicable

Re: VLOOKUP with MAX

``````// Please note I'm not using CALCULATE here
// because if the tblOrder table is big,
// it would be agonizingly slow.
[String] =
var __order = tblOrder[Order]
var __maxSEQ =
MAXX(
topn(1,
filter(
tblWork,
tblWork[Order] = __order
),
tblWork[SEQ],
DESC
),
tblWork[SEQ]
)
var __maxState =
MAXX(
topn(1,
filter(
tblWork,
tblWork[Order] = __order
&&
tblWork[SEQ] = __maxSEQ
),
tblWork[State],
DESC
),
tblWork[State]
)
var __string =
MAXX(
filter(
tblWork,
tblWork[Order] = __order
&&
tblWork[SEQ] = __maxSEQ
&&
tblWork[State] = __maxState
),
tblWork[String]
)
return
__string
``````

Best

D

Highlighted
Post Partisan

Re: VLOOKUP with MAX

Thanks for your approach! Unfortunately, I get the following error:
Calculation error in measure YXX : A single value for column 'XX' cannot be determined in table 'XX'. This can happen when a measure formula references a column that contains many values ​​without specifying an aggregation such as 'min', 'max', 'count' or 'sum' to get a single result.

Highlighted
Anonymous
Not applicable

Re: VLOOKUP with MAX

This is not a measure. It's a calculated column. This is what you asked for.

Best
D

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors