The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
79 | |
73 | |
39 | |
30 | |
28 |
User | Count |
---|---|
108 | |
99 | |
55 | |
49 | |
45 |