Dax Pattern for Matched Survey Pairs Modification

[BINewbie1]

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 pattern (Full set of measures below) is otherwise working as needed.

4 Breakdown Initial Matched =
VAR _Initial =
    // Generates a two column table of Enrolment ID and Survey Indicators ([Attribute]), where an initial score is present (i.e. anwsered questions are removed). Removes date filters so measures are always based on the date of the final survey and ignore inital surveys outside of any date filter. 
    SUMMARIZE (
        CALCULATETABLE (
            'BBBC Outcomes Framework (Before /After Scales Unpivot)',
            'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Initial Assessment",
            'pre_projectenrolment'[bbbc_enrolmentid] <> BLANK (),
            REMOVEFILTERS ( 'Calendar' )
        ),
        'pre_projectenrolment'[bbbc_enrolmentid],
        'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute]
    )
VAR _Final =
    // Generates a two column table of Enrolment ID and Survey Indicators ([Attribute]), where an final score is present (i.e. anwsered questions are removed). impilictly keeps date filters so measures are always based on the date of the final survey and ignore inital surveys outside of any date filter.
    SUMMARIZE (
        CALCULATETABLE (
            'BBBC Outcomes Framework (Before /After Scales Unpivot)',
            'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Final Assessment",
            'pre_projectenrolment'[bbbc_enrolmentid] <> BLANK ()
        ),
        'pre_projectenrolment'[bbbc_enrolmentid],
        'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute]
    )
VAR _MatchedPairs =
    // Brings together _Inital and _Final into a single two column table. Only Enrolment IDs and Survey Indicators where both an Initial score and Final score are listed.
    SUMMARIZE (
        CALCULATETABLE (
            'BBBC Outcomes Framework (Before /After Scales Unpivot)',
            _Initial,
            _Final
        ),
        'pre_projectenrolment'[bbbc_enrolmentid],
        'BBBC Outcomes Framework (Before /After Scales Unpivot)'[Attribute]
    )
VAR _InitialScore =
    // adds a new column with the first (by date), score for each enrollment/ indicator, again filters on calendar are removed so that the measure uses the final survey score as the point of reference for the matched pairs. 
    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",
                    REMOVEFILTERS ( 'Calendar' )
                )
        ),
        _MatchedPairs,
        REMOVEFILTERS ( 'Calendar' )
    )
VAR _FinalScore =
    // adds the final score and stand of the survey.
    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"
                )
        ),
        _MatchedPairs
    )
VAR _JoinedPairs =
    // connects the two tables together. If there happens to be more than two survey responses then the middle surveys are ignored.
    ADDCOLUMNS (
        FILTER (
            NATURALINNERJOIN ( _InitialScore, _FinalScore ),
            pre_projectenrolment[bbbc_enrolmentid]
                <> BLANK () // 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] )

There is another smilar measure that counts the frequency of scores for the final scores as well. They produce graphs like the ones on the right of the dashboard image below. However the code above isn't working (a different code is producing the output in the image - I'll past that 'messier' patteren in a reply).

This producing dashboards like the one below, with a dropdown to switch beween different indicators.

If anyone is interested here is the full set of measures we use for this pattern, where {...} = the variables pasted above:

* = not currently working.

1 Count of Matched Pairs = {...} RETURN Countrows(_JoinedPairs)

2 Average Initial Score (Matched) = {...} RETURN AVERAGEX(_JoinedPairs, [FirstInitialScore])

3 Average Final Score Matched = {...} RETURN AVERAGEX(_JoinedPairs, [LatestFinalScore])

*4 Breakdown Initial Matched = {...} RETURN COUNTX(_JoinedPairs, [FirstInitialScore])

*5 Breakdown Final Matched = {...} RETURN COUNTX(_JoinedPairs, [LatestFinalScore])

6 through to 10 Don't use for the full pattern as they return unmatched results:

6 Count of Unmatched Initial Surveys = CALCULATE(COUNT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value]), 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Initial Assessment")

7 Count of Unmatched Final Surveys = CALCULATE(COUNT('BBBC Outcomes Framework (Before /After Scales Unpivot)'[Value]), 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Final Assessment")

8 Average Initial (Unmatched) = CALCULATE(AVERAGEX('BBBC Outcomes Framework (Before /After Scales Unpivot)', [Value]), 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Initial Assessment")

9 Average Final (Unmatched) = CALCULATE(AVERAGEX('BBBC Outcomes Framework (Before /After Scales Unpivot)', [Value]), 'BBBC Outcomes Framework (Before /After Scales Unpivot)'[new_of_master_outcome_selector_display] = "Final Assessment")

X Percentage Change (Unmatched) = DIVIDE([9 Average Final (Unmatched)]- [8 Average Initial (Unmatched)], [8 Average Initial (Unmatched)])

[v-huijiey-msft]

Hi @BINewbie1 ,

Thank you for sharing.

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[BINewbie1]

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