Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Nyarlathorep300
Regular Visitor

Computing chi squared in Dax - missing values

I have a regularly changing dataset, on which I'm trying to create a measure that computes chi squared statistics. I want a measure so the underlying data can be filtered and the statistic will be recalcualted accordingly.

 

The raw data is surey responses for people exiting, one row per person, that hold personal information about the individual and information about motives for exiting.  I made a measure that summarizes the table by a pair of characteristics, computes the expected values, then the the mean squared variation statistic, and then sums over the rows to get the chi squared statistics.

Table = ADDCOLUMNS ( 
                  ADDCOLUMNS (
                       SUMMARIZE (
                             LeavingFactorMaster,LeavingFactorMaster[Ethnicity], LeavingFactorMaster[LeavingFactor],
                             "people", count(LeavingFactorMaster[id])
                          ), 
                         "EFx1", CALCULATE(count(LeavingFactorMaster[id]),ALL(LeavingFactorMaster[Ethnicity])),
                          "EFx2", CALCULATE(count(LeavingFactorMaster[id]),ALL(LeavingFactorMaster[LeavingFactor])),
                           "EFx3", CALCULATE(count(LeavingFactorMaster[id]),ALL(LeavingFactorMaster[LeavingFactor],LeavingFactorMaster[Ethnicity])),
                            "EFx4", divide(                                                  CALCULATE(count(LeavingFactorMaster[id]),ALL(LeavingFactorMaster[Ethnicity]))*                                            	            	CALCULATE(count(LeavingFactorMaster[id]),ALL(LeavingFactorMaster[LeavingFactor])),                                        	CALCULATE(count(LeavingFactorMaster[id]),ALL(LeavingFactorMaster[LeavingFactor],LeavingFactorMaster[Ethnicity]))
                             )
                   ), 
                   "EFx5", divide(([people]-[EFx4])^2,[EFx4])
)

 

So far so good, EFx5 looks about right, but testing the output, there is an issue. If zero people in a ethinc group gave a particular LeavingFactor answer then there is no row in the summarized table, however this combination can have a non zero expected value. This means that the chi squared stat is lower than it should be.
I've tried to gap fill using code like this.

table2 =

var _tabl = ADDCOLUMNS(                         

                                GROUPBY(

                                                UNION (

                                                                SUMMARIZE (

                                                                                LeavingFactorMaster,LeavingFactorMaster[Ethnicity], LeavingFactorMaster[LeavingFactor],

                                                                                "pp", count(LeavingFactorMaster[id])

                                                                ),

                                                                ADDCOLUMNS(

                                                                                CROSSJOIN(

                                                                                                VALUES(LeavingFactorMaster[Ethnicity]), VALUES(LeavingFactorMaster[LeavingFactor])

                                                                                ),

                                                                "pp", 0)

                                                ),

                                                [Ethnicity],[LeavingFactor],

                                                "people1",sumx(CURRENTGROUP(),[pp])

                                ), "EFx1", 1

)

return _tabl

This does ensure that the zero rows are added, but I can't work out any way of doing calculations on "people1" to compute the chi-squared statistic like the first chunk of code.

 

So my question is am I on the right track? Is can this be tweaked to work? If not, is there an different approach that will compute the chi-squared stat correctly?

3 REPLIES 3
lbendlin
Super User
Super User

Any particular reason for not using the built-in function?

 

CHISQ.DIST function (DAX) - DAX | Microsoft Learn

That function requires the input X the sum ofvthe squares of the variation from the expected values, which is the number I'm  trying to compute.

 If zero people in a ethnic group gave a particular LeavingFactor answer

Is that because there were no leavers in that group or because they left but didn't fill the survey?

 

You can consider using COALESCE to fill voids.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.