
1. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
David Li Sep 23, 2016 8:27 AM (in response to Joyce McLaren)Hi Joyce, could you clarify a bit more, or even better, attach a packaged workbook (with data sources extracted first)?
What purpose does the MIN of the first measure serve? Will it be used in a threshold of some sort?

2. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
Scott Smith Sep 23, 2016 9:00 AM (in response to Joyce McLaren)1 of 1 people found this helpfulHi Joyce,
You could use a Level of Detail calculation to find the MIN value for the Dimensions you're looking at, then test that LOD value against your measure value and return your second measure if the row is the lowest. In the case below, all records that are not the min value will be blank.
For Example
Calculated Field: IF {FIXED [Dim 1] : MIN([Value 1])} = [Value 1] THEN [Value 2] END
If you want the MIN across the entire dataset, just use IF {MIN([Value 1])} = [Value 1] THEN [Value 2] END

3. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
Joyce McLaren Sep 23, 2016 9:05 AM (in response to David Li)I need to identify the case (the row of my data) that contains the lowest value for the first measure and then report it alongside the corresponding value for another measurement.
The specific example is that I have a large data set that contains specifications about different types of energy storage installations. Each entry (rowID) includes the kilowatts (measure A) and the kilowatthours (measure B). I need to find the entry with the lowest kilowatt (measure A) value and then show it alongside the corresponding kilowatthour (measure B) value. The values of the two measures are not necessarily related. You may find the smallest A, but that doesn't mean that entry has the smallest B.
In other words, I can't just use 2 MIN() calculations because that will find the smallest value of one measure and the smallest value for the other measure, but they won't necessarily be taken from the same row.
Does that make sense?
Thanks!

4. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
David Li Sep 23, 2016 9:13 AM (in response to Joyce McLaren)I think Scott's first solution will work well for you as long as you put the dimension(s) that differentiates between energy storage installations into the FIXED part of the LOD.

5. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
Ivan Young Sep 23, 2016 10:22 AM (in response to Joyce McLaren)Hi,
I use a slightly modified version of Scott's solution which is to create a Boolean filter {FIXED [Dim 1] : MIN([Value 1])} = [Value 1] which you set to true. When the filter is applied you can select any measure or dim associated with the Min Value without creating additional fields.
Regards,
Ivan

6. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
Joyce McLaren Sep 23, 2016 2:47 PM (in response to Joyce McLaren)Thanks to all of you who replied. These solutions worked to solve that problem!
I now have another (very related) calculation that I'm trying to create and I'm getting errors that I can't overcome. (Please tell me if I need to start this in a new thread instead.)
My data looks like this, with each row being at an LOD called [Rate]:
PVsize_kW BattSize_kWh BattInverter_kW LCC LCC BAU 2795.15 1462.58 233.17 14416500 18515600 2068.64 0 0 10668600 12396900 2125.39 0 0 10302100 12316600 3289.94 3904.96 640.41 13599800 20563900 2024.6 366 169.34 9428730 10323100 2220.15 406.44 99.09 9172660 11419300 2218.26 397.69 97.26 9135230 11352300 2019.27 331.39 159.92 9462390 10383700 Using this dataset I have a calculated field called [Expected LCC Savings], which is: (MIN([LCC BAU])  MIN([LCC]))
Now I want a calculated field that finds the MAX[Expected LCC Savings] and returns the PVsize_kW, BattSize_kWh, and BattInverter_kW (either one at a time or together).
But I get the error that I can't mix aggregate and nonaggregate functions and that the argument to MAX is already aggregated and can't be further aggregated.
I tried:
IF MAX([Expected LCC Savings]) = [Expected LCC Savings] THEN [PV Size kW] END
and
IF {FIXED [Rate] : MAX([Expected LCC Savings])} = [Expected LCC Savings] THEN [PV Size kW] END
I think that Ivan's idea of creating a Boolean out of the [Expected LCC Savings] and then using it as a filter may work somehow...but I can't make it turn out right.
Thanks for any help!

7. Re: Find the MIN of one measure and return the value of another measure for that entry/rowID
Scott Smith Sep 23, 2016 3:26 PM (in response to Joyce McLaren)Since [Expected LCC Savings] is calculated field using the MIN() aggregation, any further calculations using [Expected LCC Savings] also need to be at an aggregated level. In both of your examples, [PV Size kW] is not at an aggregate level and that's causing the error.
I would recommend trying:
IF MAX({FIXED [Rate] : MAX([Expected LCC Savings])}) = [Expected LCC Savings] THEN MAX([PV Size kW]) END