GAMMA
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
