Forum Discussion

BINewbie1's avatar
BINewbie1
Helper II
1 year ago

Dax Pattern for Matched Survey Pairs Modification

Hello.   Could anyone tell me, why isn't the Countx function working correctly in the following pattern Measure, designed to count the frequency of scores for each point on a numbered scale. The pa...
"DAX "
BINewbie1's avatar
BINewbie1
Helper II
1 year ago

The measure that is working correctly is:

 

4 Breakdown Initial Matched =

VAR _Initial =

    SUMMARIZE (
        CALCULATETABLE (
            'BBBC Outcomes Framework (Before /After Scales Unpivot)',
            'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Initial Assessment",
            NOT ISBLANK('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value]),
            NOT ISBLANK('pre_projectenrolment'[bbbc_enrolmentid]),
            FORMAT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value],"Text") <> "N/A",
            FORMAT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute],"Text") <> "N/A",
            REMOVEFILTERS ( 'Calendar' )
        ),
        'pre_projectenrolment'[bbbc_enrolmentid],'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute]
    )
VAR _Final =
    SUMMARIZE (
        CALCULATETABLE (
            'BBBC Outcomes Framework (Before /After Scales Unpivot)',
            'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Final Assessment",
            NOT ISBLANK('pre_projectenrolment'[bbbc_enrolmentid]),  
            FORMAT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value],"Text") <> "N/A",            
            NOT ISBLANK('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value]),
            FORMAT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute],"Text") <> "N/A",
            NOT ISBLANK( 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute])
        ),
        'pre_projectenrolment'[bbbc_enrolmentid], 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute]
    )

VAR _MatchedPairs =
    ADDCOLUMNS( SUMMARIZE (
        CALCULATETABLE ( 'BBBC Outcomes Framework (Before /After Scales Unpivot)', _Initial, _Final ),
        'pre_projectenrolment'[bbbc_enrolmentid], 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute]
    ), "Enrolment ID and Indicator", 'pre_projectenrolment'[bbbc_enrolmentid] & 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute] )

VAR _InitialScore =
   ADDCOLUMNS( CALCULATETABLE (
        SUMMARIZE (
            'BBBC Outcomes Framework (Before /After Scales Unpivot)',
            [bbbc_enrolmentid], 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute],
            "Project",
                CALCULATE (
                    MINX (
                        TOPN ( 1, 'BBBC Outcomes Framework (Before /After Scales Unpivot)', [bbbc_all_datecompleted], ASC ),
                        'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_project]
                    ),
                    'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Initial Assessment",
                    REMOVEFILTERS ( 'Calendar' )
                ),
            "Indicator", 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute],
            "FirstInitialDate", MIN ( 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[bbbc_all_datecompleted] ),
            "FirstInitialScore",
                CALCULATE (
                    MINX (
                        TOPN ( 1, 'BBBC Outcomes Framework (Before /After Scales Unpivot)', [bbbc_all_datecompleted], ASC ),
                        'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value]
                    ),
                    'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Initial Assessment",
                    FORMAT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute],"Text") <> "N/A",
                    REMOVEFILTERS ( 'Calendar' )
                )
        ),
        FILTER( 'pre_projectenrolment', 'pre_projectenrolment'[bbbc_enrolmentid] IN SELECTCOLUMNS(_MatchedPairs, "bbbc_enrolmentid", [bbbc_enrolmentid]) ),
        REMOVEFILTERS ( 'Calendar' ) ),"Enrolment ID and Indicator", 'pre_projectenrolment'[bbbc_enrolmentid]&'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute]
    )
VAR _FinalScore =
    CALCULATETABLE (
        SUMMARIZE (
            'BBBC Outcomes Framework (Before /After Scales Unpivot)',
            [bbbc_enrolmentid], 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute],
            "LatestFinalDate", MAX ( 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[bbbc_all_datecompleted] ),
            "LatestFinalScore",
                CALCULATE (
                    MAXX (
                        TOPN ( 1, 'BBBC Outcomes Framework (Before /After Scales Unpivot)', [bbbc_all_datecompleted], DESC ),
                        'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value]
                    ),
                    'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Final Assessment",
                        FORMAT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute],"Text") <> "N/A"
                )
        ),
        FILTER( 'pre_projectenrolment', 'pre_projectenrolment'[bbbc_enrolmentid] IN SELECTCOLUMNS(_MatchedPairs, "bbbc_enrolmentid", [bbbc_enrolmentid]) )
    )
VAR _JoinedPairs =
    ADDCOLUMNS (
        FILTER (
            NATURALINNERJOIN ( _InitialScore, _FinalScore ),
            NOT ISBLANK(pre_projectenrolment[bbbc_enrolmentid]) &&
            [Enrolment ID and Indicator]  IN SELECTCOLUMNS(_MatchedPairs, "Enrolment ID and Indicator", [Enrolment ID and Indicator])
        ),
        // removes scores where there is no enrollmentlookup, otherwise they are all treated as a blank enrollment often with a percent change of 100%.
        "Difference", [LatestFinalScore] - [FirstInitialScore],
        "Percent Change", DIVIDE ( ( [LatestFinalScore] - [FirstInitialScore] ), [FirstInitialScore] )
    )

RETURN COUNTX(_JoinedPairs, [FirstInitialScore])