Tidy up current DAX measure

[Anonymous]

Hi Experts

i am trying to clean up my dax code into something more managable. not sure if it is possible to reduce the number of lines in the following code 

FROM

__freq =

VAR ComplaintsByFiscalMo =

ADDCOLUMNS (

VALUES ( PMS_FINANCIAL_PDS[Month Start] ),

"CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) )

)

VAR __table =

GROUPBY (

ComplaintsByFiscalMo,

[CountComplaints],

"__ties", COUNTX ( CURRENTGROUP (), [Month Start] )

)

VAR __table1 =

ADDCOLUMNS ( __table, "__ties1", [__ties] - 1 )

VAR __table2 =

ADDCOLUMNS (

__table1,

"__freq", IF ( [__ties1] = 0, 0, [__ties1] * ( [__ties1] + 1 ) * ( 2 * [__ties1] + 7 ) )

)

RETURN

SUMX ( __table2, [__freq] )

TO

VAR __freq =

SUMX (

GROUPBY (

ADDCOLUMNS (

VALUES ( PMS_FINANCIAL_PDS[Month Start] ),

"CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) )

),

[CountComplaints],

"__ties", COUNTX ( CURRENTGROUP (), [Month Start] )

),

IF (

[__ties] = 1,

0,

[__ties] * ( [__ties] - 1 ) * ( 2 * ( [__ties] - 1 ) + 7 )

)

)

I cannot see where i have made the error.

[amitchandak]

Can you explain what you are trying to achieve?

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

Hi Expert

I am just trying up the dax measure into a sorter formula.

[v-easonf-msft]

Hi , @Anonymous 

These two formulas seem to be equivalent .

Are the results returned by both formulas the same?

If possible , can you show me the results here?

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi Team Eason

i am trying to consolidate a few measure into one larger measure using variable but i am getting an error on Var __freq... is not recongised in Var __se

Measure 1 =
VAR __freq =
    SUMX (
        GROUPBY (
            ADDCOLUMNS (
                VALUES ( PMS_FINANCIAL_PDS[Month Start] ),
                "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) )
            ),
            [CountComplaints],
            "__ties", COUNTX ( CURRENTGROUP (), [Month Start] )
        ),
        IF (
            [__ties] - 1 = 0,
            0,
            [__ties] - 1 * ( [__ties] - 1 + 1 ) * ( 2 * [__ties] - 1 + 7 )
        )
    )
VAR __n =
    DISTINCTCOUNT ( PMS_FINANCIAL_PDS[Month Start] )
VAR __se =
    SQRT ( ( [__n] * ( [__n] - 1 ) * ( 2 * [__n] + 5 ) - [__freq] ) / 18 )
VAR __S =
    SUMX (
        ADDCOLUMNS (
            VALUES ( PMS_FINANCIAL_PDS[Month Start] ),
            "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),
            "Index", RANKX ( PMS_FINANCIAL_PDS, PMS_FINANCIAL_PDS[Month Start],,, DENSE )
        ),
        SUMX (
            ADDCOLUMNS (
                VALUES ( PMS_FINANCIAL_PDS[Month Start] ),
                "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),
                "Index", RANKX ( PMS_FINANCIAL_PDS, PMS_FINANCIAL_PDS[Month Start],,, DENSE )
            ),
            IF (
                [Index] > EARLIER ( [Index] )
                    && [CountComplaints] < EARLIER ( [CountComplaints] ),
                1,
                IF (
                    [Index] > EARLIER ( [Index] )
                        && [CountComplaints] > EARLIER ( [CountComplaints] ),
                    -1
                )
            )
        )
    )
VAR __zstat =
    IF (
        [__S] > 0,
        ( [__S] - 1 ) / [__se],
        IF ( [__S] < 0, ( [__S] + 1 ) / [__se], 0 )
    )
RETURN
    2 * NORM.S.DIST(-ABS(__zstat),TRUE)