Counting Columns for a Dynamic Measure

Figured out a way to do it within one measure. Thanks to ValtteriN for helping me understand that I needed to generate a new table of column names from the frequency table. COLUMNSTATISTICS() would only work if the frequency table existed in the model, which is not very pratical when you already have a very large model. To keep it all within the same measure, I created a datatable in the measure code which holds the data of the frequency table. By counting the rows without blanks in that table, you get a dynamic calculation of the rows. Then you can unpivot this table in the same code and then distinct select the column with the column names. From there you create a new column with the totals for these two categories from which you can count rows without blanks to get the number of columns in the frequency table. With a row count and a column count, you can create a dynamic calculation of degrees of freedom to plug into both the calculation of your p-value and the visual. Pretty cool to have a dynamic card next to your bar graph and frequency table that gives you a precise X^2 with the corresponding p-value for any filter that you apply to the report. Here is the code, and I am also attaching a first draft of the report.

Chi Square = 

Var NonePassed_O = Calculate(Count('Courses/Exams'[Exam Module Result]), FILTER('Courses/Exams', 
'Courses/Exams'[Course Status] = "None"
&& 'Courses/Exams'[Exam Module Result] = "Passed"))

Var NoneFailed_O = Calculate(Count('Courses/Exams'[Exam Module Result]), FILTER('Courses/Exams', 
'Courses/Exams'[Course Status] = "None"
&& 'Courses/Exams'[Exam Module Result] = "Not Passed"))

Var InsuffPassed_O = Calculate(Count('Courses/Exams'[Exam Module Result]), FILTER('Courses/Exams', 
'Courses/Exams'[Course Status] = "Insufficient"
&& 'Courses/Exams'[Exam Module Result] = "Passed"))

Var InsuffFailed_O = Calculate(Count('Courses/Exams'[Exam Module Result]), FILTER('Courses/Exams', 
'Courses/Exams'[Course Status] = "Insufficient"
&& 'Courses/Exams'[Exam Module Result] = "Not Passed"))

Var SuffPassed_O = Calculate(Count('Courses/Exams'[Exam Module Result]), FILTER('Courses/Exams', 
'Courses/Exams'[Course Status] = "Sufficient"
&& 'Courses/Exams'[Exam Module Result] = "Passed"))

Var SuffFailed_O = Calculate(Count('Courses/Exams'[Exam Module Result]), FILTER('Courses/Exams', 
'Courses/Exams'[Course Status] = "Sufficient"
&& 'Courses/Exams'[Exam Module Result] = "Not Passed"))

Var SumRow1 = NonePassed_O + NoneFailed_O

Var SumRow2 = SuffPassed_O + SuffFailed_O

Var SumRow3 = InsuffPassed_O + InsuffFailed_O

Var SumColumn1 = NonePassed_O + SuffPassed_O + InsuffPassed_O

Var SumColumn2 = NoneFailed_O + SuffFailed_O + InsuffFailed_O

Var SumRows = SumRow1 + SumRow2 + SumRow3

Var SumColumns = SumColumn1 + SumColumn2

Var NonePassed_E = (SumRow1 * SumColumn1) / SumRows

Var SuffPassed_E = (SumRow2 * SumColumn1) / SumRows

Var InsuffPassed_E = (SumRow3 * SumColumn1) / SumRows

Var NoneFailed_E = (SumRow1 * SumColumn2) / SumRows

Var SuffFailed_E = (SumRow2 * SumColumn2) / SumRows

Var InsuffFailed_E = (SumRow3 * SumColumn2) / SumRows

Var Chi_Square = (NonePassed_O - NonePassed_E)^2/NonePassed_E + (NoneFailed_O - NoneFailed_E)^2/NoneFailed_E + (SuffPassed_O - SuffPassed_E)^2/SuffPassed_E + (SuffFailed_O - SuffFailed_E)^2/SuffFailed_E + (InsuffPassed_O - InsuffPassed_E)^2/InsuffPassed_E + (InsuffFailed_O - InsuffFailed_E)^2/insuffFailed_E

Var tab_ = DATATABLE("Course_Status", STRING,
                        {  
                        {"None"},  
                        {"Insufficient"},  
                        {"Sufficient"}
                }  
           )

Var tab2_ = ADDCOLUMNS(tab_,
    "Passed_", IF([Course_Status] = "None", NonePassed_O,
                IF([Course_Status] = "Insufficient", InsuffPassed_O,
                IF([Course_Status] = "Sufficient", SuffPassed_O, BLANK()))),
    "Not_Passed", IF([Course_Status] = "None", NoneFailed_O,
                IF([Course_Status] = "Insufficient", InsuffFailed_O,
                IF([Course_Status] = "Sufficient", SuffFailed_O, BLANK())))
                )

Var tab3_ = FILTER(Tab2_, NOT(ISBLANK([Passed_])) && NOT(ISBLANK([Not_Passed])) )

Var Rows_ = COUNTROWS(tab3_)

Var ColumnsTab_ = UNION(
    SELECTCOLUMNS(tab3_, "Course_Status_", [Course_Status], "Exam_Result", "Passed", "Count", [Passed_]),
    SELECTCOLUMNS(tab3_, "Course_Status_", [Course_Status], "Exam_Result", "Failed", "Count", [Not_Passed])
)

Var ColumnsTab2_ = Distinct(SELECTCOLUMNS(ColumnsTab_,
        "Columns", [Exam_Result]))

Var ColumnsTab3_ = ADDCOLUMNS(ColumnsTab2_,
                "Total", IF([Columns] = "Passed", NonePassed_O + InsuffPassed_O + SuffPassed_O,
                        IF([Columns] = "Failed", NoneFailed_O + InsuffFailed_O + SuffFailed_O,
                        BLANK()))
)

Var ColumnsTab4_ = FILTER(ColumnsTab3_, NOT(ISBLANK([Total])))

Var Columns_ = Countrows(ColumnsTab4_)

Var DF_ = (Rows_ - 1) * (Columns_ - 1)

Var PVal_ = CHISQ.DIST(Chi_Square, DF_, FALSE)

Var PVal2_ = IF(PVal_ < 0.001, ", p < 0.001", ", p = " & ROUND(PVal_,3))

Var Output_ = "X^2 " & " (" & DF_ & ", N = " & SumRows & ") = " & ROUND(Chi_Square,2) & PVal2_ 

RETURN Output_

3 X 22 X 2