Hello, I have a table in PowerPivot that I would like to return a value based on 2 parameters. The first is easy as I can directly reference the value. The second is harder as I need the highest k value that is less than or equal to the probability value that is provided. I will attempt to demonstrate below:

Scenario:
qsigma = 0.58
probability = 0.0086880

Here is part of the relevant table from the PowerPivot (name = SafetyFactorTable). (NOTE: all 3 columns are Data Type: Decimal Number)

SafetyFactorTable.PNG

I have created a measure KFactor:=Average(SafetyFactorTable[k])

For the above example, the formula should return a K Factor of 1.92, to get this value the set is limited to qsigma = 0.58. From that list, the greatest k with a probability that is less than or equal to the provided 0.008688. In this case, the probability of 0.008488 is the closest without exceeding the provided value.

I can get the result with CUBEVALUE and CUBEMEMBER functions. I believe that I need to create a CUBESET and then use CUBERANK to return the K Factor. I am unable to create a CUBESET expression that returns anything other than #N/A.

Here is the non dynamic formula that is successful:
Formula: copy to clipboard 
Please Login or Register  to view this content.

Here is the current CUBESET that fails:
Formula: copy to clipboard 
Please Login or Register  to view this content.

I believe the error is occurring because the 0.0086880 is not a member present in the data set. But I am failing in figuring out the correct syntax to fix this. I am able to use cell references to make these formula's work, but removed those for simplification.

I greatly appreciate any help that is provided.

Thanks!
Ryan