Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |