In my recent quest to create or catalog as many DAX equivalents for Excel functions was able to leverage my work on GAMMA to come up with F.DIST.
F.THIS =
VAR __x = [x]
VAR __Deg1 = [Deg_freedom1]
VAR __Deg2 = [Deg_freedom2]
VAR __GAMMA1 =
VAR __zInput = (__Deg2 + __Deg1) / 2
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
VAR __GAMMA2 =
VAR __zInput = (__Deg1) / 2
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
VAR __GAMMA3 =
VAR __zInput = (__Deg2) / 2
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(__GAMMA1,__GAMMA2 * __GAMMA3) *
POWER(__Deg1/__Deg2,__Deg1/2) *
DIVIDE(
POWER(__x,(__Deg1-2)/2),
POWER(1+(__Deg1/__Deg2)*__x,(__Deg1+__Deg2)/2)
)
The cumulative form of F.DIST is:
F.THIS.CUMULATIVE =
VAR __x = [x]
VAR __df1 = [Deg_freedom1]
VAR __df2 = [Deg_freedom2]
RETURN
BETA.DIST(
__x*__df1/(__x*__df1+__df2),
__df1/2,
__df2/2,
TRUE
)F.DIST.RT is:
F.THIS.RT = 1 - [F.THIS.CUMULATIVE]F.INV is this:
F.INV =
VAR __p = [F.THIS.CUMULATIVE]
VAR __df1 = [Deg_freedom1]
VAR __df2 = [Deg_freedom2]
RETURN
BETA.INV(__p,__df1/2,__df2/2) * __df2/(__df1*(1-BETA.INV(__p,__df1/2,__df2/2)))
And last but not least, F.INV.RT:
F.INV.RT =
VAR __p = 1 - [F.THIS.RT]
VAR __df1 = [Deg_freedom1]
VAR __df2 = [Deg_freedom2]
RETURN
BETA.INV(__p,__df1/2,__df2/2) * __df2/(__df1*(1-BETA.INV(__p,__df1/2,__df2/2)))Thank goodness for actual documentation:
- https://support.minitab.com/en-us/minitab-express/1/help-and-how-to/basic-statistics/probability-dis...
- http://www.real-statistics.com/chi-square-and-f-distributions/f-distribution/
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Mastering Power BI 2nd Edition
DAX is easy, CALCULATE makes DAX hard...
