Forum Discussion
Getting Data From Previous Row Based On Two Criteria
I have have the table 'Data' with the fields 'employee ID', 'rotation number', and 'location' with each employee ID appearing three times with rotation number increasing each time and a location code per row. I am trying to add a 'last location' calculated column that will display the last location by looking at the rotation number and subtracting 1 from it, unless the rotation number is 1 which gets the string "xxxxx" like this:
+---------------+-------------------+------------+-----------------+ | employee ID | rotation number | location | last location | +---------------+-------------------+------------+-----------------+
1001 1 abcde xxxxx 1001 2 fghij abcde 1001 3 klmno fghij 1002 1 pqrst xxxxx 1002 2 uvwx pqrst 1002 3 yzab uvwx
Can anyone help with this?
5 Replies
Is the last value in 'last location' a mistake? Should it be uvwx?
If so, try adding a column
Column = var _emp = Table1[employee ID] var _rot = Table1[rotation number] RETURN if (Table1[rotation number] <> 1,
CALCULATE(MAX(Table1[location]),FILTER(Table1, Table1[employee ID] = _emp && Table1[rotation number] < _rot)),
"xxxxx")
HotChilli wrote:Is the last value in 'last location' a mistake? Should it be uvwx?
Yes, that was a mistake. I've fixed it now.
HotChilli wrote:If so, try adding a column
The column kinda works, but it is only pulling the entry for rotation #2 and then displaying it for all rows after the first:
+---------------+-------------------+------------+-----------------+ | employee ID | rotation number | location | last location | +---------------+-------------------+------------+-----------------+
1001 1 abcde xxxxx 1001 2 fghij abcde 1001 3 klmno abcde 1002 1 pqrst xxxxx 1002 2 uvwx pqrst 1002 3 yzab pqrstDo you want to link your pbix here and i'll have a look at it?
