LOOKUPVALUE returning multiple values - I want to grab the largest value

[SearchingYT]

Hi Power BI experts,

I have a simple question regarding lookupvalue function returning multiple values.

How can I code it to return the highest of those multiple values only. In my case it is a date, and I want it to return the latest date.

Thanks

[SearchingYT · Accepted solution]

I was able to solve this using CALCULATE function, taking the max of my date column and filtering lookup result;

Calculate(max(table.dateColumn), filter(table, table.id = mytable.id))

Hope this helps someone.

[BrianCSCKOF]

It worked perfectly for me, I used it with average instead of max, great contribution.

[johnt75]

Rather than LOOKUPVALUE you can use SELECTCOLUMNS .. TOPN, e.g.

Max Value =
SELECTCOLUMNS ( TOPN ( 1, 'Table', 'Table'[Date] ), "@val", 'Table'[Date] )

In the event that you have multiple entries with the same date then you would need to add another column to the TOPN ranking to ensure only a single value is returned, ideally a unique identifier column or an index column added through Power Query