Dynamic change calculation - initial vs last per client

DAX DAX Calculations
1

Hi there

I have shared a sample PBI file.
I can’t determine why results in Table 1 do not replicate the results in Table 2 and would extremely appreciate guidance on how to achieve this.
Output required is Table 1 displaying Table 2 results which would allow for date context filter - Table 2 results are correct and Table 1 are incorrect.

Table 1 uses Measures to grab Initial and Last Scores per client, which means the results are dictated by the date context and are dynamic in nature.
Table 2 uses a combination of M and Calculated Columns to grab Initial and Last scores per client, which means the results will not be affected by the date context and are static in nature.

The method for calculation is consistent for both tables which is the following;
RCI:
- 1.96*Standard error of change
- Standard error of change =
- Standard Deviation (population) of client * √(2) * √(1-0.93)

1.96*(Standard Deviation (population) of client * √(2) * √(1-0.93))

Change = Last Score-Initial Score

Change more than RCI = reliable improvement
Change less than RCI = reliable deterioration
else Reliable Static

Please feel free to suggest new measures / modifications to existing measures to provide required output.

Please also let me know if I can provide any further information.

sample OMs.pbix (674.7 KB)

Thanks
Peter

1 Like
2

Hi @PeterF - Please share the base spreadsheet i.e. xlsx file to look into it.

Thanks
Ankit J

3

Measures.xlsx (313.9 KB)

See attached!

4

Hi @PeterF - Try below and check if you are getting expected results.

Reliable Deterioration =

var RcIndex = 1.96 *(

    STDEVX.P(

        VALUES( 'OMs'[ClientID] ),

            [Initial Score]

    ) *( SQRT( 2 ) ) *( SQRT( 1 -0.93 ) ))

    return

Divide(

    Countx(

        VALUES( 'OMs'[ClientID] ),

            if([Difference] > RCindex,"Yes")),

    Countx(

        VALUES( 'OMs'[ClientID] ),

            [Difference]))

Thanks
Ankit J

2 Likes
5

Thanks @ankit - very helpful for Deterioration and Improvement measures, however I can’t replicate the desired result for Static.
Suggestions?

Reliable Static =

var RcIndex = 1.96 *(

STDEVX.P(

    VALUES( 'OMs'[ClientID] ),

        [Initial Score]

) *( SQRT( 2 ) ) *( SQRT( 1 -0.93 ) ))

return

Divide(

Countx(

    VALUES( 'OMs'[ClientID] ),

        if(and([Difference] <= RCindex, [Difference] >= -RCindex),"Yes") ),

Countx(

    VALUES( 'OMs'[ClientID] ),

        [Difference]))
6

Hi @PeterF - Try below.

> Reliable Static 1 = 
> 
> var RcIndex = 1.96 *(
> 
>     STDEVX.P(
> 
>         VALUES( 'OMs'[ClientID] ),
> 
>             [Initial Score]
> 
>     ) *( SQRT( 2 ) ) *( SQRT( 1 -0.93 ) ))
> return
> Divide(
>     Countx(
>         VALUES( 'OMs'[ClientID] ),
>             if(Not(isblank([Difference])) && [Difference] <=RCindex && [Difference] >=-RCindex,"Yes")),
>     Countx(
>         VALUES( 'OMs'[ClientID] ),
>             [Difference]))

Thanks
Ankit J

1 Like
7

Hi @PeterF, did the response provided by @ankit help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

8

Thank you so much for your help @ankit