GAMMA

Greg_Deckler — Sat, 09 May 2020 17:26:45 GMT

In my recent quest to create or catalog as many DAX equivalents for Excel functions, this is an approximation of the Gamma function using Lanczos' approximation. Had to learn Python to get it coded in DAX since the Wikipedia page had the example code in Python. So...I learned that today.

GAMMA = VAR __zInput = MAX('Table'[z]) VAR __p = { (0, 676.5203681218851), (1, -1259.1392167224028), (2, 771.32342877765313), (3, -176.61502916214059), (4, 12.507343278686905), (5, -0.13857109526572012), (6, 9.9843695780195716e-6), (7, 1.5056327351493116e-7) } VAR __EPSILON = 1e-7 VAR __z = IF(__zInput < 0.5, 1 - __zInput - 1,__zInput - 1) VAR __pTable = ADDCOLUMNS( __p, "x",[Value2] / (__z + [Value1] + 1) ) VAR __x = 0.99999999999980993 + SUMX(__pTable,[x]) VAR __t = __z + COUNTROWS(__pTable) - .5 VAR __y = IF( __zInput < 0.5, PI() / (SIN(PI() * __zInput) * SQRT(2*PI()) * POWER(__t,__z+0.5) * EXP(-1*__t) * __x), SQRT(2*PI()) * POWER(__t,__z+0.5) * EXP(-1*__t) * __x ) RETURN __y

I think the cool part of this is that the original Python code had a for loop in it and a recursive element to it but I was able to code it in DAX even though DAX has no looping or recursion. So that was pretty nifty.

I had originally used Stirling's approximation. That is GAMMA1. I wouldn't use that one though, it is not nearly as accurate especially for low values of z < .5. But, here it is anyway.

GAMMA1 = VAR __z = MAX('Table'[z]) RETURN EXP( .5 * (LN ( 2 * PI() ) - LN(__z) ) + __z * ( LN( __z + 1 / ( 12 * __z - 1 / 10 * __z) ) - 1 ) )

We can use our new GAMMA function to also calculate GAMMALN, GAMMALN.PRECISE and GAMMA.DIST

GAMMALN = LN([GAMMA]) GAMMA.DIST = VAR __x = [x] VAR __Alpha = [Alpha] VAR __Beta = [Beta] VAR __GAMMA = VAR __zInput = __Alpha VAR __p = { (0, 676.5203681218851), (1, -1259.1392167224028), (2, 771.32342877765313), (3, -176.61502916214059), (4, 12.507343278686905), (5, -0.13857109526572012), (6, 9.9843695780195716e-6), (7, 1.5056327351493116e-7) } VAR __EPSILON = 1e-7 VAR __z = IF(__zInput < 0.5, 1 - __zInput - 1,__zInput - 1) VAR __pTable = ADDCOLUMNS( __p, "x",[Value2] / (__z + [Value1] + 1) ) VAR __x = 0.99999999999980993 + SUMX(__pTable,[x]) VAR __t = __z + COUNTROWS(__pTable) - .5 VAR __y = IF( __zInput < 0.5, PI() / (SIN(PI() * __zInput) * SQRT(2*PI()) * POWER(__t,__z+0.5) * EXP(-1*__t) * __x), SQRT(2*PI()) * POWER(__t,__z+0.5) * EXP(-1*__t) * __x ) RETURN __y RETURN DIVIDE( POWER(__x,__Alpha - 1) * EXP(-1*__x/__Beta), POWER(__Beta,__Alpha) * __GAMMA )

I have not yet been able to solve the cumulative form of the GAMMA.DIST function or the GAMMA.DIST.INV

eyJrIjoiYzUyNGUxNjQtYmIyOS00ZWFhLTlkODEtNDIyZGNmMGUyYTViIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

topic GAMMA in Quick Measures Gallery

GAMMA