cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

VLOOKUP with MAX

Hello everyone!

I have two tables:

tblOrder

OrderDateValue
100101.01.2020500
100201.01.2020 

 

tblWork

OrderSEQStateString
1001501AAA
1001502

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

Helpful resources

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