How to create a lookup between a calculated column and a measure

[Anonymous]

X(Calculated Coulmn)	CDF(Measure)
100	.11
150	.24
200	.30

I have a CDF probaility table created with x value as caclulated column and CDF value f(x) with a measure. Now i hope to get quartiles of the CDF . Like i want the value of x where CDF is 0.25. How can i do that? Kindly help

[Anonymous · Accepted solution]

// There must be a table with a column
// from which you can choose the quantile
// percentage, like .25. The table that
// stores x and cdf(x) must start with x
// for which cdf(x) = 0 and end with x
// for which cdf(x) = 1. Here's a measure
// that returns the value of the quantile
// (linear approximation). Q table stores
// the percentages you'd like to calculate
// quantiles for. CDF table stores (x, cdf(x)).

[Quantile] =
var __percentage = SELECTEDVALUE( Q[Level], 2 )
var __cdfLessThanOrEqualToPercentage =
    TOPN(1
        FILTER(
            ALL( CDF ),
            CDF[cdf] <= __percentage
        ),
        CDF[cdf],
        DESC
    )
var __cdfGreaterThanPercentage =
    TOPN(1,
        FILTER(
            ALL( CDF ),
            CDF[cdf] > __percentage,
        ),
        CDF[cdf],
        ASC
    )
var __quantileEstimate =
    if(
        NOT ISEMPTY( __cdfGreaterThanPercentage ),
        
        var x1 = 
            MINX( 
                __cdfLessThanOrEqualToPercentage,
                CDF[x]
            )
        var y1 = 
            MINX( 
                __cdfLessThanOrEqualToPercentage,
                CDF[cdf]
            )
        var x2 =
            MINX(
                __cdfGreaterThanPercentage,
                CDF[x]
            )
        var y2 = 
            MINX(
                __cdfGreaterThanPercentage,
                CDF[cdf]
            )
        return
            divide( x2 - x1, y2 - y1)
                * (__percentage - y1)
                + x1
    )
return
    __quantileEstimate

[Anonymous]

Hello, thank you for your help with this! In my model, only x is a column in a table. The CDF values are a measure which i was not able to convert into a calculated column. Therefore i am not able to implement your DAX in my model as it uses CDF with functions like ALL which i cannot use as my CDF is a measure. How can i find my quantiles?

[Anonymous]

@Anonymous

If the measure only depends on the x value, then it's very easy to create a calculated table that will store x and cdf(x). Why can't you do this? But even if CDF is a measure, it should be pretty easy to adjust this code I gave you to calculate the quantiles. You just have to work with x and a measure instead of with a column.

[Anonymous]

Hello, made some updates and the Dax works!

Thank you so much for helping me with this. I probably could not have figured it out as am just getting started with Dax.

Thank you!

[lbendlin]

You will have to write another measure that does the quartile computation independently of your first measure.