Counting Columns for a Dynamic Measure

[MJEnnis]

Is there a way to count the number of columns in a data table? Obviously you can count rows, but I also need to count the columns, dynamically.

I am creating a measure for a visual that reports the results of a chi square test. However, applying different filters can remove rows and, technically, columns from the chi square table. The p-value for a chi square (returned from the

CHISQ.DIST() function) depends upon the degrees of freedom. The formula for calculating the degrees of freedom is:  (rows − 1) × (columns − 1).

My solution is to create a datatable within the code of my measure for the sole purpose of making the calculation of the degrees of freedom dynamic. This is my start, excluding the previous calculations of observed values, expected values, etc. Note that I think I will need to add some conditions to COUNTROWS() so as to not count rows with blank values. But that part is easy enough. I am stuck on dynamically counting the columns in tab_.

...

Var tab_ = DATATABLE("Course_Status", STRING,

"Passed_", INTEGER,

"Not_Passed", INTEGER,{

{"None_", NonePassed_O, NoneFailed_O},

{"Insufficient",InsuffPassed_O, InsuffFailed_O},

{"Sufficient",SuffPassed_O, SuffFailed_O}

}

)

Var DF_ = (COUNTROWS(tab_)-1) * (2 - 1)

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

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

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

[MJEnnis · Accepted solution]

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

[ValtteriN]

Hi,

One way to achieve this is to use COLUMNSTATISTIC like this:

Measure 19 =

var _table = FILTER(COLUMNSTATISTICS(),[Table Name] = "Table (7)") return

countrows(_table)-1

This will count the columns of "Table (7)". See this for more information: https://dax.guide/columnstatistics/


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[MJEnnis]

Hmm... very interesting solution. However, my table only exists in the measure code, not in the model itself. So I would have to create a new table in the model for this to work, right? 

For instance, in the following, [Table Name] is greyed out. 

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))),

"Not_Passed", IF([Course_Status] = "None", NoneFailed_O,

IF([Course_Status] = "Insufficient", InsuffFailed_O,

IF([Course_Status] = "Sufficient", SuffFailed_O)))

)

Var Rows_ = COUNTROWS(tab2_)

Var Columnstat_ = FILTER(COLUMNSTATISTICS(), [Table NAME] = "Tab2_")

Var Columns_ = COUNTROWS(Columnstat_) - 1

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

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

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

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