Forum Discussion

SevsBo's avatar
SevsBo
Responsive Resident
3 years ago

Calculated table to get earliest highest value?

I am trying to find the earliest occurances of the highest value in a given column.    Here is the initial state of the data:   Item Cost Date a1 1 01/01/2022 a1 3 01/05/2022 a1...
Need Help
johnt75's avatar
johnt75
Super User
3 years ago

You can create a calculated table like

My Table =
INDEX(
	1,
	'Table',
	ORDERBY( 'Table'[Cost], DESC, 'Table'[Date], ASC),
	PARTITIONBY( 'Table'[Item]),
	MATCHBY( 'Table'[Item], 'Table'[Date])
)
SevsBo's avatar
SevsBo
Responsive Resident
3 years ago

Just for reference, it needed a Blank argument as well, I chose "Default".

 

I am getting an error though:

 

Despite of MatchBy columns being specified, duplicated rows are encountered in INDEX's Relation parameter. This is not allowed. The current operation was cancelled because another operation in the transaction failed.

 

I think this might be because some Items might have the same date and rating twice, will try to dedupe that part on query level and then run this again.