Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
It worked perfectly for me, I used it with average instead of max, great contribution.
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
User | Count |
---|---|
89 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |