topic Re: Difference Between Two Related Rows in a Table in DAX Commands and Tips

Difference Between Two Related Rows in a Table

samdep — Wed, 05 Apr 2023 18:48:42 GMT

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!

Date

Index

Name

Type

Manager

Mgr & Report

Judgment Rating

Judgment Variance

3/31/2023 4:00pm

John

Assessment of Report

John-Sally

4/2/2023 5:00pm

Sally

Self-Assessment

John

John-Sally

Re: Difference Between Two Related Rows in a Table

amitchandak — Thu, 06 Apr 2023 02:00:51 GMT

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

Re: Difference Between Two Related Rows in a Table

Anonymous — Tue, 25 Apr 2023 06:41:55 GMT

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