Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |