FirstSortedValue with Set Analysis

anthonyeeles — Mon, 16 Jan 2023 15:32:11 GMT

Hi everyone,

I am struggling to get a "lowest value" type solution. I have a table which compares the price and distance of pairs of locations in my database, like this:

LocationID	ComparedLocationID	ComparedLocationName	ComparedLocationPrice	ComparedDistance
12355	12272	Location 844	26	0.18912645163311
12355	12386	Location 752	30	0.27766228681346
12355	12762	Location 345	40	0.31924088539052
12355	12518	Location 541	36	0.34147527922903
12355	12545	Location 586	36	0.34147527922903
12355	12476	Location 84	38	0.41288297293363
12355	12353	Location 880	48.5	0.53549753898979
12355	12335	Location 220	48.5	0.57002562592256
12355	12758	Location 780	26	0.66663773606327
12355	12080	Location 26	30	0.70992792036625
12355	12222	Location 43	35	0.71534669915399
12355	12701	Location 574	40	0.7449042128228
12355	12703	Location 808	40	0.79096353125306
12355	10123	Location 676	38	0.81746758231713
12355	12618	Location 351	27	0.90440058147854
12355	12728	Location 532	40	0.92123159170605
12355	12617	Location 68	40	1.0075602368222
12355	12354	Location 816	48.5	1.2126078777738
12355	12795	Location 802	26	1.3312047153253
12355	12590	Location 868	38	1.3314522539974
12355	10114	Location 719	38	1.3794920387649
12355	12702	Location 293	40	1.4490457603866
12355	10752	Location 72	26	1.6013994998343
12355	12879	Location 540	30	1.627419095761
12355	12580	Location 558	25	1.6767955213875

The dimension in my chart is LocationID. I am then trying to get the ComparedLocationName of the Location with the lowest ComparedLocationPrice as long as the ComparedDistance is within, say, 5 miles.

I had assumed I could use FirstSortedValue with some set analysis:

=FirstSortedValue({<ComparedDistance={'<=5'}>} ComparedLocationName, ComparedLocationPrice)

The expression evaluates OK, but returns empty cells.

Googling seems to suggest I can't use Set Analysis in FirstSortedValue, but I can't seem to find an alternative. Other examples use an AGGR but I'm not clear how to do that in this context.

All suggestions welcome!

Thanks!

Re: FirstSortedValue with Set Analysis

SerhanKaraer — Thu, 19 Jan 2023 20:48:57 GMT

Hello anthon,

Try this expression:

Concat({<ComparedLocationPrice={"$(=min({<ComparedDistance={"<=5"}>} TOTAL ComparedLocationPrice))"}>} ComparedLocationName, ';')

If there happens to be multiple comparedlocationnames, you can show all them.

topic FirstSortedValue with Set Analysis in App Development

FirstSortedValue with Set Analysis

Re: FirstSortedValue with Set Analysis