topic How to create a lookup between a calculated column and a measure in DAX Commands and Tips

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

Anonymous — Mon, 24 Aug 2020 15:58:15 GMT

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

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

lbendlin — Mon, 24 Aug 2020 19:05:39 GMT

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

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

Anonymous — Mon, 24 Aug 2020 22:36:38 GMT

// 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

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

Anonymous — Tue, 25 Aug 2020 08:13:47 GMT

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?

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

Anonymous — Tue, 25 Aug 2020 09:58:17 GMT

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.

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

Anonymous — Tue, 25 Aug 2020 14:26:00 GMT

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!