Re: Per Cent Change Calculation in Survey Results

adamlang · ‎03-16-2024

Hi,

I'm trying to create a measure that will provide the average percentage change in wellbeing score across multiple survey results, based on what we call matched pairs.

I need the measure to:

- Calculate the average percent change between surveys, in given date period (determined by the visualisation, and a relationship to my calendar table - in this example a year)

- It must only look at enrollments that have a score for both the initial and final surveys - a matched pair of results. Blank/null values must be removed from both initial and final surveys.

- The initial survey can sit outside of an applied date filter - i.e. if a table is filtered to look at the percentage change in 2023, it can do this by including initials surveys from before that year, basically the date context will be taken only from the final survey.

- There are sometime more than one initial or final survey under the same enrollment, in such cases I need the measure to

- For initial surveys use the survey score from the first initial survey date

- For final surveys use the survey score from the most recent/ latest survey date

- Sometimes there are two surveys of the same type on the same day, in which case I need the measure to:

- for initial surveys use the lowest score

- for final surveys use the highest score

Test Data:

Enrollment ID	Survey Type	Gen Well Being Score	Survey Date
ERL01	Initial	3	01 January 2023
ERL01	Final	6	01 June 2023
ERL02	Initial		01 January 2023
ERL03	Initial		02 January 2023
ERL03	Final	4	01 June 2023
ERL02	Final		01 June 2023
ERL04	Initial	5	01 February 2023
ERL04	Initial	5	01 February 2023
ERL05	Initial	3	01 November 2022
ERL05	Final	4	01 February 2023
ERL01	Initial	2	20 December 2022
ERL05	Final	5	15 February 2023
ERL06	Initial	1	20 December 2022
ERL06	Initial	2	20 December 2022
ERL06	Final	6	20 March 2023
ERL06	Initial	3	15 December 2022
ERL07	Initial	2	20 June 2022
ERL07	Final	6	15 December 2022

.pbix file:

https://1drv.ms/u/s!ArkjHR6LyR4yg1-jGsfmUVdv25JF?e=EstFT2

(edited link)

Expected result

Year	Average Per Cent Change in Wellbeing
2022	200%
2023	244%

Explanation:

In the test data there are only four matched pairs of survey results: ERL01, ERL05, ERL06, ERL07. Where there is a score for both initial and final surveys.

ERL07 is the only matched pair relating to 2022 (when the final survey was carried out). The change was 2 ->6 which is a 200% increase (difference in scores divided by original score).

For 2023, the relevant scores are:

Enrolment	First Initial WB Score	Latest Final WB Score	Per Cent Change
ERL01	2	6	200%
ERL05	3	4	33.3%
ERL06	1	6	500%

Current Measure:

With help from this forum (thanks Greg!), I have a starting point in terms of the DAX:

Average Percent Change v2 =
VAR __Valid =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                FILTER ( ALL ( 'Outcome Surveys' ), [Gen Well Being Score] <> BLANK () ),
                [Enrollment ID ],
                "__Count", COUNTROWS ( 'Outcome Surveys' )
            ),
            [__Count] >= 2
        ),
        "__id", [Enrollment ID ]
    )
VAR __Table =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    'Outcome Surveys',
                    [Enrollment ID ]
                        IN __Valid
                            && [Survey Type] = "Final"
                ),
                "__Prev",
                    VAR __id = [Enrollment ID ]
                    VAR __Result =
                        MINX (
                            FILTER (
                                ALL ( 'Outcome Surveys' ),
                                [Enrollment ID ] = __id
                                    && [Survey Type] = "Initial"
                            ),
                            [Gen Well Being Score]
                        )
                    RETURN
                        __Result
            ),
            "__Diff", [Gen Well Being Score] - [__Prev]
        ),
        "__Percent", DIVIDE ( [__Diff], [__Prev], 0 )
    )
VAR __Result =
    AVERAGEX ( __Table, [__Percent] )
RETURN
    __Result

However, this version is not correctly managing the instances of multiple initial or final surveys in the way I need described above.

I think I need to insert something to ensure that for each enrollment only one matched pair of results is used, as mentioned the initial score needs to remove any date filter so a score can be drawn from a previous period, so I think needs an ALL:

VAR __Survey =
    SUMMARIZE (
        'Outcome Surveys',
        'Outcome Surveys'[Enrollment ID ],
        "First WB Score",
            MINX (
                TOPN ( 1, 'Outcome Surveys', [Survey Date], ASC ),
                'Outcome Surveys'[Gen Well Being Score]
            ),
        "Last WB Score",
            MAXX (
                TOPN ( 1, 'Outcome Surveys', [Survey Date], DESC ),
                'Outcome Surveys'[Gen Well Being Score]
            )
    )

Many thanks,

Adam

adamlang · ‎04-20-2024

Hi All

Sorry to nudge this thread but just a reminder that if anyone is willing and able I'd like support finished off this Dax puzzle. The post of ‎04-01-2024 03:15 PM, explains where I've got to. Basically I have some dax which seeks to generate a percentage change between a initital survey score and a finial survey score. The dax needs to do an amount of data manipulation due to messy data, but I have sorted out most of that now. Remaining issue is that I need to correctly modify any date filter on the initail survey because in the final presentation of the result the table filter (in the example a Calendar Year) needs not to filter the initial survey. The reason is because I need to present the percentage change based on the date of the final score, potencailly including where the initial survey was carried out in a previous year.

The OneDrive shared link of the .Pbix file (in the post mentioned) loads with a message saying that preview within OneDrive is not availible, but the file can simply be downloaded.

Let me know if you have any questions. I'm hoping that the following varible can be modified with an ALL, or REMOVEFILTERS, to give the result I need. The removefilters I've included doesn't appear to be working as needed.

VAR _InitialScore =

CALCULATETABLE (

SUMMARIZE (

'Outcome Surveys',

[Enrollment ID ],

"FirstInitialDate", MIN ( 'Outcome Surveys'[Survey Date] ),

"FirstInitialScore", CALCULATE ( MINX (TOPN ( 1, 'Outcome Surveys', [Survey Date], ASC ), 'Outcome Surveys'[Gen Well Being Score]), 'Outcome Surveys'[Survey Type] = "Initial")

),

FILTER('Outcome Surveys', [Enrollment ID ] IN _MatchedPairs),

REMOVEFILTERS ( 'Outcome Surveys'[Survey Date] )

)

Many thanks,

Adam

v-yilong-msft · ‎03-18-2024

Hi @adamlang ,

Can you chang the way to upload the .pbix file? When I open the link, I can only see the blank page. However, I think you can try to change the DAX code.

Average Percent Change v2 =
VAR InitialSurveys =
    CALCULATETABLE (
        SUMMARIZE (
            'Outcome Surveys',
            [Enrollment ID],
            "FirstInitialDate", MIN ( 'Outcome Surveys'[Survey Date] ),
            "FirstInitialScore", CALCULATE ( MIN ( 'Outcome Surveys'[Gen Well Being Score] ), FILTER ( 'Outcome Surveys', [Survey Type] = "Initial" ) )
        ),
        FILTER ( 'Outcome Surveys', [Survey Type] = "Initial" ),
        REMOVEFILTERS ( 'Outcome Surveys'[Survey Date] )
    )
VAR FinalSurveys =
    SUMMARIZE (
        'Outcome Surveys',
        [Enrollment ID],
        "LatestFinalDate", MAX ( 'Outcome Surveys'[Survey Date] ),
        "LatestFinalScore", CALCULATE ( MAX ( 'Outcome Surveys'[Gen Well Being Score] ), FILTER ( 'Outcome Surveys', [Survey Type] = "Final" ) )
    )
VAR MatchedPairs =
    NATURALINNERJOIN ( InitialSurveys, FinalSurveys )
VAR PercentChanges =
    ADDCOLUMNS (
        MatchedPairs,
        "PercentChange", DIVIDE ( [LatestFinalScore] - [FirstInitialScore], [FirstInitialScore] )
    )
RETURN
    AVERAGEX ( PercentChanges, [PercentChange] )

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

adamlang · ‎03-19-2024

Thanks @v-yilong-msft ,

I'm grateful for the response.

I'll take a look at how I'm uploading the file, it might be a dropbox setting, I'm only a free user and it doesn't seems to allow a full download. Might try another provider.

The Output of your measure, which I've called v3 is a follows:

Not really sure what's going on.

Thanks,

Adam

adamlang · ‎03-19-2024

Does this link work? I've switched to a OneDrive account:

Outcome Survey Matched Pair Test v2.pbix

Thanks,

Adam

adamlang · ‎03-24-2024

Thanks @v-yilong-msft,

I've done a bit of analysis with your measure in Query View, and I think its almost there.

I just need the dax to filter out all the survey results that aren't valid matched pairs. To illustrate, the varible MatchedPairs, currently looks like this:

The following enrollments don't have 'matched pairs' i.e. both a valid initial survery score and an valid final survey score : ERL02, ERL03, and ERL04. These need to be removed from the average calculation. The filter applied in the previous dax simply used a does not equal blank to acheive this.

I've tried this in the following query:

EVALUATE

VAR InitialSurveys =

CALCULATETABLE (

SUMMARIZE (

'Outcome Surveys',

[Enrollment ID ],

"FirstInitialDate", MIN ( 'Outcome Surveys'[Survey Date] ),

"FirstInitialScore", CALCULATE ( MIN ( 'Outcome Surveys'[Gen Well Being Score] ), FILTER ( 'Outcome Surveys', [Survey Type] = "Initial" ) )

),

FILTER ( 'Outcome Surveys', [Survey Type] = "Initial"),

FILTER('Outcome Surveys', [Gen Well Being Score] <> Blank ()),

REMOVEFILTERS ( 'Outcome Surveys'[Survey Date] )

)

VAR FinalSurveys =

SUMMARIZE (

'Outcome Surveys',

[Enrollment ID ],

"LatestFinalDate", MAX ( 'Outcome Surveys'[Survey Date] ),

"LatestFinalScore", CALCULATE ( MAX ( 'Outcome Surveys'[Gen Well Being Score] ), FILTER ( 'Outcome Surveys', [Survey Type] = "Final" ), FILTER ('Outcome Surveys', [Gen Well Being Score] <> Blank() ),

)

VAR MatchedPairs =

NATURALINNERJOIN ( InitialSurveys, FinalSurveys )

VAR PercentChanges =

ADDCOLUMNS (

MatchedPairs,

"PercentChange", DIVIDE ( [LatestFinalScore] - [FirstInitialScore], [FirstInitialScore] )

)

RETURN

MatchedPairs

But while the added filter seemed to work on VAR InitialSurveys, the similar filter throws up a syntax error in VAR FinalSurveys.

After the filters are applied correctly, I can check with the measure is handling the survey days as required.

Many thanks for any support that can be offered.

Adam

adamlang · ‎03-24-2024

@v-yilong-msft

So I've now solved the syntax error with the following, but it doesn't appear to be filtering as I need:

EVALUATE

VAR InitialSurveys =

CALCULATETABLE (

SUMMARIZE (

'Outcome Surveys',

[Enrollment ID ],

"FirstInitialDate", MIN ( 'Outcome Surveys'[Survey Date] ),

"FirstInitialScore", CALCULATE ( MIN ( 'Outcome Surveys'[Gen Well Being Score] ), FILTER ( 'Outcome Surveys', [Survey Type] = "Initial" ) )

),

FILTER ( 'Outcome Surveys', [Survey Type] = "Initial"),

FILTER('Outcome Surveys', [Gen Well Being Score] <> Blank ()),

REMOVEFILTERS ( 'Outcome Surveys'[Survey Date] )

)

VAR FinalSurveys =

SUMMARIZE (

'Outcome Surveys',

[Enrollment ID ],

"LatestFinalDate", MAX ( 'Outcome Surveys'[Survey Date] ),

"LatestFinalScore", CALCULATE ( MAX ( 'Outcome Surveys'[Gen Well Being Score] ), FILTER ( 'Outcome Surveys', [Survey Type] = "Final" && 'Outcome Surveys'[Gen Well Being Score] <> Blank() ))

)

VAR MatchedPairs =

NATURALINNERJOIN ( InitialSurveys, FinalSurveys )

VAR PercentChanges =

ADDCOLUMNS (

MatchedPairs,

"PercentChange", DIVIDE ( [LatestFinalScore] - [FirstInitialScore], [FirstInitialScore] )

)

RETURN

MatchedPairs

But its still not actually filtering the valid matched pairs as I need, ERL04 is still appearing in the table dispite not having a valid final survey score.

Is there anywhere else I can apply the <> Blank () filter?

Thanks,

Adam

v-yilong-msft · ‎03-28-2024

Hi @adamlang ,

I'm sorry to reply to you so late, I can't open your link, if you can upload it in a different way, I think I can do further research.

Also I think you can provide more information about <> Blank() filter

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

adamlang · ‎03-28-2024

Hi @v-yilong-msft,

No worries, any help you can offer much appreciated.

Does this link work? - its produced by one drive with a 'anyone with the link can edit' authorisation:

https://1drv.ms/u/s!ArkjHR6LyR4yg1-jGsfmUVdv25JF?e=Au3eGD

Should come up with a message saying that preview isn't possible but there's a download button just below? It should work, but if not I'll find another online storage provider - not sure what I'm doing wrong. Might try a googledrive account. Thanks for staying engaged!

Regarding the filter to remove blanks, I need the final average calculation to be based only on enrollment where both a initial survey and a final survey have been completed. So I need to remove the enrollments where either the initial survey score, or the final survey score is blank.

The updated query seems to be filtering the initial surveys as needed, but the ERL04 enrollment needs to be removed because there's no final survey score. Although not in the test data, a zero is a valid score, so I think filtering blanks is the best way?

I asked a similar question in another post previously (I messed up the test data and ended with a measure that didn't do what I needed). The measure created by Greg in that post did manage the filtering correctly - but not other requirements stated in this post. Might be worth a look, i've not been able to replicate it correctly:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Survey-Results-Matched-Pairs/m-p/370...

Many thanks,

Adam

adamlang · ‎04-01-2024

Hi @v-yilong-msft,

I've made a bit of a step forward with this now. In the following measure, I think it using the right scores now. Its just the remove date filter on the initial scores which I don't think its working as I need it to.

It might be something to do with how I've joined the _Initial and _Final varible tables together, I'm not sure.

Average Percent Change v3 =

VAR _Initial = SUMMARIZE(CALCULATETABLE('Outcome Surveys', 'Outcome Surveys'[Survey Type] = "Initial", 'Outcome Surveys'[Gen Well Being Score] <> BLANK(), REMOVEFILTERS ( 'Outcome Surveys'[Survey Date] )),

'Outcome Surveys'[Enrollment ID ])

VAR _Final = SUMMARIZE(CALCULATETABLE('Outcome Surveys', 'Outcome Surveys'[Survey Type] = "Final", 'Outcome Surveys'[Gen Well Being Score] <> BLANK()),

'Outcome Surveys'[Enrollment ID ])

VAR _MatchedPairs = SUMMARIZE(CALCULATETABLE('Outcome Surveys', _Initial, _Final), 'Outcome Surveys'[Enrollment ID ])

VAR _InitialScore =

CALCULATETABLE (

SUMMARIZE (

'Outcome Surveys',

[Enrollment ID ],

"FirstInitialDate", MIN ( 'Outcome Surveys'[Survey Date] ),

"FirstInitialScore", CALCULATE ( MINX (TOPN ( 1, 'Outcome Surveys', [Survey Date], ASC ), 'Outcome Surveys'[Gen Well Being Score]), 'Outcome Surveys'[Survey Type] = "Initial")

),

FILTER('Outcome Surveys', [Enrollment ID ] IN _MatchedPairs),

REMOVEFILTERS ( 'Outcome Surveys'[Survey Date] )

)

VAR _FinalScore =

CALCULATETABLE (

SUMMARIZE (

'Outcome Surveys',

[Enrollment ID ],

"LatestFinalDate", MAX ( 'Outcome Surveys'[Survey Date] ),

"LatestFinalScore", CALCULATE ( MAXX (TOPN ( 1, 'Outcome Surveys', [Survey Date], DESC ), 'Outcome Surveys'[Gen Well Being Score]), 'Outcome Surveys'[Survey Type] = "Final" )

),

FILTER ('Outcome Surveys', [Enrollment ID ] IN _MatchedPairs)

)

VAR _JoinedPairs =

ADDCOLUMNS(ADDCOLUMNS(NATURALINNERJOIN ( _InitialScore, _FinalScore ),

"Difference", [LatestFinalScore] - [FirstInitialScore]),

"Percent Change", DIVIDE( [Difference], [FirstInitialScore])

)

VAR _Averagepercentchange =

AVERAGEX(_JoinedPairs, [Percent Change])

RETURN

_Averagepercentchange

This provides the result:

* No. of Matched pairs is simply a count rows of _JoinedPairs, which it used to work out how the measure is interacting with the applied date filter, the Calendar Year.

The result should be:

Year	Average Percent Change	Number of Matched Pairs	Notes
2022	200%	1	ERL07
2023	122.2%	3	ERL01, ERL05, ERL06
Total	141.67	4

I'm pretty sure the measure is wrong because the remove date filter code isn't doing what is required. The measure should only take into account the date of the final survey when determining the average percent change for each year. A matched pair can include an initial survey in 22, and a final survey in 23, but should only be reported in 23.

I've updated the .pbix file here,

Outcome Survey Matched Pair Test v3.pbix

Many thanks,

Adam