Forum Discussion

samdep's avatar
samdep
Advocate II
3 years ago
Solved

Difference Between Two Related Rows in a Table

Hi Community,

 

I have a table, similar to the below, where I'd like to show the difference between the manager's rating of the direct report and the direct report's rating of themselves. I'd like to create a measure that looks at the difference between the manager's rating and the direct report's rating, and from there, add a flag within the cell if the difference between the two ratings is greater than or equal to 2.

 

Previously, I had used the following DAX measure and it worked fine, but now it's throwing an error with 'EARLIER'. 

 

Judgment Variance = 
VAR _MAX = MAXX(FILTER('Assessments', 'Assessment'[Index] < EARLIER([Index]) && 'Assessment{'Manager & Direct Report'] = EARLIER('Assessment{'Manager & Direct Report'])), [Index]

 

VAR _RATING = 'Assessment'[Judgment] - MAXX(FILTER('Assessments', 'Assessment'[Index] < EARLIER([Index]) && 'Assessment{'Manager & Direct Report'] = EARLIER('Assessment{'Manager & Direct Report'])), 'Assessment'[Judgment]

 

RETURN

IF(_RATING = 'Assessment'[Judgment], BLANK(),
IF(_RATING = 0, BLANK(), ABS(_RATING))
)

 

Appreciate any guidance!

 

DateIndexNameTypeManagerMgr &  ReportJudgment RatingJudgment Variance
3/31/2023 4:00pm1JohnAssessment of Report John-Sally51
4/2/2023 5:00pm2SallySelf-AssessmentJohnJohn-Sally4

 

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi samdep ,

    First, please make sure that what you are creating is a calculated column not a measure, then update its formula as below to get the expected result:

    Judgment Variance = 
VAR _nextindex =
    MINX (
        FILTER (
            'Assessment',
            'Assessment'[Index] > EARLIER ( [Index] )
                && 'Assessment'[Manager & Direct Report] = 'Assessment'[Manager & Direct Report]
        ),
        [Index]
    )
VAR _nextrating =
    MAXX (
        FILTER (
            'Assessment',
            'Assessment'[Index] = _nextindex
                && 'Assessment'[Manager & Direct Report] = EARLIER ( 'Assessment'[Manager & Direct Report] )
        ),
        [Judgment]
    )
RETURN
    IF (
        ISBLANK ( _nextrating )
            || _nextrating = 'Assessment'[Judgment],
        BLANK (),
        ABS ( 'Assessment'[Judgment] - _nextrating )
    )

    Best Regards

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    samdep , try like

     

    Judgment Variance =
    VAR _MAX = MAXX(FILTER('Assessments', 'Assessment'[Index] < EARLIER([Index]) && 'Assessment'[Manager & Direct Report] = EARLIER('Assessment'[Manager & Direct Report])), 'Assessment'[Index])

     

    VAR _RATING = 'Assessment'[Judgment] - MAXX(FILTER('Assessments', 'Assessment'[Index] =_max && 'Assessment'[Manager & Direct Report] = EARLIER('Assessment'[Manager & Direct Report])), 'Assessment'[Judgment])

     

    RETURN

    IF(_RATING = 'Assessment'[Judgment], BLANK(),
    IF(_RATING = 0, BLANK(), ABS(_RATING))
    )

     

     

    Power BI DAX- Earlier, I should have known Earlier: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

    Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi samdep ,

    First, please make sure that what you are creating is a calculated column not a measure, then update its formula as below to get the expected result:

    Judgment Variance = 
VAR _nextindex =
    MINX (
        FILTER (
            'Assessment',
            'Assessment'[Index] > EARLIER ( [Index] )
                && 'Assessment'[Manager & Direct Report] = 'Assessment'[Manager & Direct Report]
        ),
        [Index]
    )
VAR _nextrating =
    MAXX (
        FILTER (
            'Assessment',
            'Assessment'[Index] = _nextindex
                && 'Assessment'[Manager & Direct Report] = EARLIER ( 'Assessment'[Manager & Direct Report] )
        ),
        [Judgment]
    )
RETURN
    IF (
        ISBLANK ( _nextrating )
            || _nextrating = 'Assessment'[Judgment],
        BLANK (),
        ABS ( 'Assessment'[Judgment] - _nextrating )
    )

    Best Regards