Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@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])
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |