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.
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?
Solved! Go to Solution.
@HotChilli Thats alright, I found a workaround solution by adding an index column and then calculating index-1 from it. Thanks for trying, though.
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 pqrst
Do you want to link your pbix here and i'll have a look at it?
@HotChilli Thats alright, I found a workaround solution by adding an index column and then calculating index-1 from it. Thanks for trying, though.
Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |