topic F.DIST in Quick Measures Gallery

F.DIST

Greg_Deckler — Wed, 13 May 2020 03:52:38 GMT

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:

eyJrIjoiOGRlZDc0OTUtMjE3MC00Mjg4LTgxZTktMDEzMDkxNzEzZjUxIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: F.DIST

Barendnu — Tue, 31 Aug 2021 12:10:39 GMT

Thank you very much! I was looking for the equivalent of Statistics.FDistribution in DAX, and this works great, very handy indeed!

Re: F.DIST

MJEnnis — Fri, 09 Feb 2024 14:10:23 GMT

This is pretty awesome! I thought it was weird that PowerBI has functions for the Beta distribution, but not the F distribution. I spent a few hours reflecting on how to use the T distribution or Beta distribution functions to calcuate F.INV. I needed it desparately to dynamically calculate confidence intervals to report a measure of interclass correlation. I was just about to give up and write a complaint to Microsoft when I stumbled upon this great post! Thanks a lot, Greg_Deckler! This is not the first time you have helped me through such problems!

Re: F.DIST

MJEnnis — Sat, 10 Feb 2024 13:29:04 GMT

Pasting in an example of the F.INV measure being used in a report to calculate the confidence intervals for an interclass correlation coefficient. Pretty cool to get a dynamic calcuation that works with filters!

Re: F.DIST

MJEnnis — Sat, 10 Feb 2024 13:41:56 GMT

And here is the code to calculate ICC with interpretation and confidence intervals.

Test_Retest_ICC = VAR n = COUNTROWS('TEST_RETEST') VAR nT = n*2 VAR k = 2 VAR AvgT = DIVIDE(SUM('TEST_RETEST'[Test Score]) + SUM('TEST_RETEST'[Retest Score]),nT) VAR SST = SUMX('TEST_RETEST', ('TEST_RETEST'[Test Score]-AvgT)*('TEST_RETEST'[Test Score]-AvgT)) + SUMX('TEST_RETEST', ('TEST_RETEST'[Retest Score]-AvgT)*('TEST_RETEST'[Retest Score]-AvgT)) VAR SSW = SUMX('TEST_RETEST', ('TEST_RETEST'[Test Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k)) * ('TEST_RETEST'[Test Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k)) + ('TEST_RETEST'[Retest Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k)) * ('TEST_RETEST'[Retest Score]-DIVIDE('TEST_RETEST'[Retest Score]+'TEST_RETEST'[Test Score],k))) VAR SSB = SST-SSW VAR DFW = (n*k)-n VAR DFB = n-1 VAR MSB=DIVIDE(SSB,DFB) VAR MSW=DIVIDE(SSW,DFW) VAR ICC = (MSB-MSW)/(MSB+((k-1)*MSW)) Var CCT = SWITCH(TRUE, ICC>=-1 && ICC<0 ,"Invalid Estimate", ICC=0 ,"No Agreement", ICC>0 && ICC<0.4 ,"Poor Agreement", ICC>=0.4 && ICC<0.6 ,"Fair Agreement", ICC>=0.6 && ICC<0.75 ,"Good Agreement", ICC>=0.75 && ICC<1 ,"Excellent Agreement", ICC=1 ,"Perfect Agreement" ) VAR F = MSB/MSW VAR alpha = 0.05 VAR alpha_Tail = alpha/2 /* The next two variables are taken from the F.INV measure provided by Greg. Ideal to report confidence intervals with the ICC, and you need F.INV to calculate those. This is but just one example of a practical application of Greg's measure. It can also be used for ANOVA stats, for example. Until the PBI developers add F.DIST and F.INV DAX functions, I will be borrowing this measure! */ VAR FINV_L = BETA.INV(1-alpha_Tail,DFB/2,DFW/2) * DFW/(DFB*(1-BETA.INV(1-alpha_Tail,DFB/2,DFW/2))) VAR FINV_U = BETA.INV(1-alpha_Tail,DFW/2,DFB/2) * DFB/(DFW*(1-BETA.INV(1-alpha_Tail,DFW/2,DFB/2))) VAR F_L = DIVIDE(F,FINV_L) VAR F_U = F*FINV_U VAR LOWER_ = DIVIDE(F_L-1,F_L+k-1) VAR UPPER_ = DIVIDE(F_U-1,F_U+k-1) RETURN CCT & ", ICC(1,1)=" & ROUND(ICC,3) & ", 95% CI [" & ROUND(LOWER_,3) & ", " & ROUND(UPPER_,3) &"]" & " (n=" & n & ")"