Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

F.DIST

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...
Mathematical
Other
MJEnnis's avatar
MJEnnis
Icon for Resolver III rankResolver III
2 years ago

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!

 

No filter appliedMultiple filters applied

MJEnnis's avatar
MJEnnis
Icon for Resolver III rankResolver III
2 years ago

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 & ")"