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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
adamlang
Helper III
Helper III

Per Cent Change Calculation in Survey Results

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

9 REPLIES 9
adamlang
Helper III
Helper III

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
Community Support
Community Support

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.

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:

 

adamlang_0-1710831636956.png

Not really sure what's going on.

 

Thanks,

 

Adam

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

 

Outcome Survey Matched Pair Test v2.pbix

 

Thanks,

 

Adam

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:

adamlang_0-1711276651733.png

 

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

 

@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.
 
adamlang_0-1711280336248.png

 

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

 

Thanks,

 

Adam

 

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.

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

 

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:
adamlang_1-1711979672043.png

* 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:
 
YearAverage Percent ChangeNumber of Matched PairsNotes
2022200%1ERL07
2023122.2%3ERL01, ERL05, ERL06
Total141.674 
 
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, 
 
Many thanks,
 
Adam

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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