Paired T-Test Using DAX

[Anonymous]

I'm working with clinical datasets that include test scores for pre-treatment versus post-treatment (same clients). Some post-treatment data are missing. Here's an example of how my data looks like:

Research ID	Time 1 Score	Time 2 Score
1	8	7
2	6	NA
3	7	5
4	9	4
5	5	NA

I was wondering if we could use DAX to perform a paired sample t-test. I don't really need a visual, just need something like this to show whether it's significant and whether it has enough power:

Sample Size	n = 5 (this would be how many included in paired analysis)
Significance Level	(Not significant / Not enough power / Missing data / * / ** / ***)

So the final result will depend on the sample size, the power, and the p value.

Can someone please help me with this? 🙂

[JarroVGIT · Accepted solution]

Hi @Anonymous ,

I had to refresh my memory on paired T test, always fun to brush up statistic methods (no sarcasm). However, I am a bit unclear on what you are trying to achieve. Couple of questions:
1. What do you expect the output to be exactly? I mean, we can create a measure that returns N (basically the COUNTROWS() of the dataset). However, for the second part, what does *, **, *** mean, when is "missing data" returned and what p-value is the border for not significant? Typically * = 0.05, ** = 0.01 and *** is 0.001 (at least, when I was in university that was the case if memory serves me well). Are you expecting "Missing Data" to be returned given your dataset with missing data in your second sample? And what does 'Not enough power' mean?
2. Sometimes you start numbering questions but end up asking a bunch under 1. So no second question 😛 

I liked the use case so I made a sample for you. I used a different dataset but the principle remains the same of course. Please find my pbix attached, hope this helps you out. The main DAX is of course the significance measure.

Significance = 
//We first extend our sample data, but filter out missing Score2 data. We add a difference column and a difference^2 column
VAR _tmpTableForT = ADDCOLUMNS(FILTER(PairedT, ISERROR(PairedT[Score2]) = FALSE), 
    "Diff", PairedT[Score1] - PairedT[Score2], 
    "DiffSQ", (PairedT[Score1] - PairedT[Score2])^2)

//We create a few variables that make calculating the T-value easier.
VAR sumDiff = SUMX(_tmpTableForT, [Diff])
VAR sumDiffSQ = SUMX(_tmpTableForT, [DiffSQ])
VAR sumDiffColSQ = sumDiff^2
VAR N = COUNTROWS(_tmpTableForT)

//We calculate the T_Score
VAR T_nominator = sumDiff/N
VAR T_denominator = SQRT((sumDiffSQ - (sumDiffColSQ / N)) / ((N-1)*N))
VAR T_Score = ABS(T_nominator/T_denominator)

//We lookup the T-value of the three significance levels. First we assess the row to compare against based on N-1
VAR deg = SWITCH(TRUE(),
    N-1 <= 30, N-1,
    N-1 <= 45, 30,
    N-1 <= 90, 60,
    N-1 <= 560, 120,
    1000)
VAR T_0_05 = LOOKUPVALUE(T_Table[Sig_0.05], T_Table[DegreesOfFreedom], deg)
VAR T_0_01 = LOOKUPVALUE(T_Table[Sig_0.01], T_Table[DegreesOfFreedom], deg)
VAR T_0_001 = LOOKUPVALUE(T_Table[Sig_0.001], T_Table[DegreesOfFreedom], deg)

//If the T_score  is bigger than the T_sig, then it is at least that significance level. So, if T_score < T_0_05, it is not significant. If T_Score > T_0_001, then significance is  ***
VAR result = SWITCH(TRUE(),
    T_Score > T_0_001, "*** p=0.001",
    T_Score > T_0_01, "** p=0.01",
    T_Score > T_0_05, "* p=0.05",
    "Not significant")
RETURN
result

In this sample I do not account for empty values. If you want to throw a 'missing values', you can adjust the last SWITCH statement by adding a first check like < N < COUNTROWS(PairedT), "Missing data", >. See my PBIX attached, this was a fun question to answer! 🙂 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

Hi @JarroVGIT 

Thank you so much for looking into my question!

The sample size measure that returns N seems pretty straightfoward. I was going to use IF statements but I like your function better since I rely on IF statements way too much LOL. So here's what I want the second part to return:

If the power of the paired t-test is less than .8, it returns "not enough power" regardless of the P value

If the power is .8 or higher, it will run the paired sample t-test. If results not significant, returns "not significant". If results significants, returns * , ** , *** (you were right on what these mean)

I thought about it a bit more and I don't need it to return "missing data", because that will only be the case when we're looking at one participant at a time, and there's not enough power anyways.

The DAX functions you provided make sense to me at first glance, I will look into the pbix file in a bit and double check the mathematical calculations involved. Here is the part I don't understand so far:

//We lookup the T-value of the three significance levels. First we assess the row to compare against based on N-1
VAR deg = SWITCH(TRUE(),
    N-1 <= 30, N-1,
    N-1 <= 45, 30,
    N-1 <= 90, 60,
    N-1 <= 560, 120,
    1000)

It seems like something to do with degrees of freedom but I'm not so sure. Sorry about that!

Thanks again ^^

[JarroVGIT]

Hi @Anonymous ,

I don't know how to calculate the power of the current dataset. I Googled a bit but I can't figure out the math quickly (I am a drone by now, about to go to sleep 😉 ) If you can give me the formula of calculating the Power, I can put it into the last SWITCH statement as a first check. 

About the Degrees of Freedom, these are N-1 in a T-test. To get the corresponding T-value from the T-value Table, I need to find the corresponding row based on Degrees of Freedom. However, most T-value Tables are looking at degree 1-30 individually, but then jump to 60, 120 and then 1000 because the T-values are pretty much similar. So, if your dataset is 500 rows, then your degree of freedom is 499. How are you going to lookup the corresponding T-value in the T-value Table? There is an entry for 120 and an entry for 1000, but not for 499. 

So, what that SWITCH does is looking at your (N-1), and determine which row should be looked at in the T-value Table. If your dataset is < 46 (=N-1<45), then take the T-value of the T-table where degree is 30. If it is bigger then 45 but smaller than 90, look at T-table where degree is 60, etc. Have a look at the T-value Table and you will see what I am trying to do 🙂

Have a good night!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

Hi @JarroVGIT 

I looked into calculating the power, and it looks like most websites use 0.8 power as the default and then figure out what's the minimum sample size needed. I suppose we could do it like this as well in this case (if the backwards thinking makes it more complicated)? So in this case if our sample size is smaller than required sample size for .8 power then we know there's not enough power. Here are a few links that have some formulas and explanations on how to calculate it (I like the last one the best):

https://www.statsdirect.com/help/sample_size/paired_t_test.htm

http://statulator.com/SampleSize/ss2PM.html

https://www.sample-size.net/sample-size-study-paired-t-test/

Another thing is that I'm looking for two-tailed tests 🙂

Thanks a lot!

[JarroVGIT]

Hi @Anonymous 

All these websites (and a whole bunch more, I looked at them all) I also found on the first page of Google 😉 I am no statistician, and I can't figure it out given the information that I have. Best I can see is that given a certain certainty (0.05) and some standard deviation you can calculate it, but it just elludes me precisely how. I have many sites that have a calculator, but some (for example, your last one that you liked) ask for stuff as 'effect size', a variable that you haven't mentioned. Next to that, your current logic doesn't make any sense. You calculate an minimum N based on significance and power. So let's say we have a formula that determines the current N is big enough to have a power of 0.8' with significance 0.05, then what does significance of * * or  * * * mean? You don't know the power of those significances.

To me, at this point, I would really recommend not doing this in Power BI but in a more suitable application like SPSS or something, maybe R? 

Hope this helps!

[Anonymous]

I think it would make more sense and be easier to let Power BI run the R code. Thanks for the suggestion @JarroVGIT  !

[JarroVGIT]

You are very welcome. Aside from the matter on power, would you consider this question answered? I think others that would Google similar questions would be helped by my initial answer and PBIX. 🙂