I am trying to add an expression in a chart that shows 'Unit' grouped by Product with the max truckQty (and max unitQty). Or even concat multiple results if it returns more than 1 result for max truckQty.
LOAD * INLINE[ Product, Unit, truckQty, unitQty a, aa, 10000, 100 a, bb, 5000, 200 a, cc, 7000, 100 a, dd, 10000, 250 b, aa, 10000, 200 b, bb, 8000, 300 b, cc, 5000, 250 b, dd, 11000, 250 ];
FirstSortedValue(distinct Unit, -truckQty) returns the following as expected.
a | aa
b | dd
However, my question is how do I get 'a' to return 'dd' (largest truck qty and largest unit qty). I have tried the aggr function but have not been able to get the expected results.
Thank you in advance for taking the time to help me....