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.
We have training data that looks like below:
TrainingID | Name | ResponseID | Score | Latest Score | Difference from Previous Score |
123 | A | 7125417 | 1 | 2 | - |
123 | A | 7125423 | 2 | 2 | +1 |
123 | A | 7125461 | 3 | 2 | +1 |
123 | A | 7125465 | 2 | 2 | -1 |
234 | B | 7125955 | 1 | 3 | - |
234 | B | 7125962 | 2 | 3 | +1 |
234 | B | 7125978 | 3 | 3 | +1 |
234 | B | 7125985 | 3 | 3 | 0 |
345 | A | 7125997 | 1 | 4 | - |
345 | A | 7134183 | 2 | 4 | +1 |
345 | A | 7134191 | 3 | 4 | +1 |
345 | A | 7134196 | 4 | 4 | +1 |
345 | B | 7134204 | 1 | 3 | - |
345 | B | 7134254 | 2 | 3 | +1 |
345 | B | 7134267 | 3 | 3 | +1 |
A "Name" can complete a given "TrainingID" however many times they want. Each time they compelete a TrainingID, they get a "Score". The training event is stored as a transaction in the database by "RecordID"; record IDs increment up 1 for each training event, so it can be used to order things chronologically 1-N, where item 1 was completed before item 2, etc.
The first event of a given TrainingID for a Name, I don't need to return a value. All other events, I need to return the following: Latest Score (red) column and Difference from Previous Score (blue) column. I don't necessarily need the "+" in front of the positive increments, but I definitely need a negative value where they got a higher score the previous event than the lastest event.
I've got the Latest Score (red) column working by using a Group By function. Where I'm stuck is getting the Difference from Previous Score (blue) column.
I've tried creating a measure with the following, but it's not returning the values based on the Training ID and Name, just the Training ID.
Solved! Go to Solution.
Very much the same formula, just use OFFSET instead of INDEX.
Latest Score = MAXX(INDEX(-1,ALL(Query1),PARTITIONBY([TrainingID],[Name]),MATCHBY([ResponseID])),[Score])
Difference =
var a = MAXX(OFFSET(-1,ALL(Query1),PARTITIONBY([TrainingID],[Name]),MATCHBY([ResponseID])),[Score])
return if(not ISBLANK(a),[Score]-a)
Very much the same formula, just use OFFSET instead of INDEX.
Latest Score = MAXX(INDEX(-1,ALL(Query1),PARTITIONBY([TrainingID],[Name]),MATCHBY([ResponseID])),[Score])
Difference =
var a = MAXX(OFFSET(-1,ALL(Query1),PARTITIONBY([TrainingID],[Name]),MATCHBY([ResponseID])),[Score])
return if(not ISBLANK(a),[Score]-a)
Yes - that was it! Thank you SO much! I don't know why I didn't think to use OFFSET!
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 |
---|---|
52 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
75 | |
58 | |
47 | |
16 | |
12 |