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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
samdep
Advocate II
Advocate II

Absolute Value Difference Between Two Non-Consecutive Rows (Same IDs)

Hi PBI Community!

 

I have a table similar to the below:

 

DATE             ID          GPA  

12/1/2021    ABCD     3.8

12/1/2021    ABCD     4.0

 

I am using tables in my visual and would like to include a flag for any variance in the GPA greater than 1.5 between the same ID/Student's GPA. So, in this case, ID: ABCD would have a variance of .2 -- The GPA's could come in on the same date, which is why I am having a bit of a struggle. Also, I'd like the absolute value, as opposed to perhaps a negative value.

 

I may put the .2 in the table itself or write a conditional expression that says something along the lines of 'if the variance is >= 1.5, then Flag, ""'

 

Thanks All - Very much appreciate all of the help provided in this community!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@samdep , This bit difficult. You need to add an index column in power query -

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

Then create a measure like 

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index])) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max ) , [GPA])

 

or


New column =
var _max = maxx(filter(Table, [Index] <earlier([Index]) && [ID] = earlier([ID] )) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max && [ID] = earlier([ID] ) ) , [GPA])

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@samdep , This bit difficult. You need to add an index column in power query -

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

Then create a measure like 

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index])) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max ) , [GPA])

 

or


New column =
var _max = maxx(filter(Table, [Index] <earlier([Index]) && [ID] = earlier([ID] )) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max && [ID] = earlier([ID] ) ) , [GPA])

Awesome, thank you! This worked well for me - Index tip was great, hadn't noticed that in PQ. Very helpful! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.