Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello I need help in creating a column that references another row and populates a future assignment. We assign employees to projects and each assignment comes into the report as a single row. Some employees have a current assignment and future assignment, so they have (2) rows coming into the report. I would like to reference the future assignment row and populate a "Future Assignment Name" column on the row of their current assignment. Would also like to reference the start and end dates of the future assignment row. The reference between rows should be matched by the person name.
Sample Data:
Assignment Table
Index | Person Name | Assignment Name | Assignment Start Day | Assignment End Day | Current Assignment | Next Assignment | Future Assignment Name (need help creating this column) | Future Assignment Start Day (need help creating this column) |
0 | John Doe | Project Alpha | November 16, 2020 | March 26, 2021 | TRUE | 0 | Project Beta | October 4, 2021 |
1 | John Doe | Project Beta | October 4, 2021 | October 28, 2022 | FALSE | 1 | (BLANK) | (BLANK) |
Solved! Go to Solution.
Hi, @dacosta
You can create the following Calculated columns to get the result you want.
Column 1
Rank by Person name =
RANKX (
FILTER ( ALL ( Assignments ), [person_name] = EARLIER ( [person_name] ) ),
[Assignment Start Day],
,
ASC,
DENSE
)
Column 2
Future Assignment Name =
CALCULATE (
MAX ( 'Assignments'[Assignment Name] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
Column 3
CALCULATE (
MAX ( 'Assignments'[Assignment Start Day] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solutions to help the other members find it more quickly.
Hi, @dacosta
You can create the following Calculated columns to get the result you want.
Column 1
Future Assignment Name =
VAR midT1 =
FILTER (
ALL ( Assignments ),
AND (
Assignments[person_name] <= EARLIER ( Assignments[person_name] ),
Assignments[Next Assignment] = 1
)
)
var midT2=FILTER(midT1,MIN(Assignments[Assignment Start Day]))
VAR val =
CALCULATE ( SELECTEDVALUE ( Assignments[Assignment Name] ), midT2 )
VAR nam =
CALCULATE ( SELECTEDVALUE ( Assignments[person_name] ), midT2 )
RETURN
IF (
Assignments[person_name] = nam
&& Assignments[Next Assignment] = 0,
val,
BLANK ()
)
Column 2
Future Assignment Start Day =
VAR midT1 =
FILTER (
ALL ( Assignments ),
AND (
Assignments[person_name] <= EARLIER ( Assignments[person_name] ),
Assignments[Next Assignment] = 1
)
)
var midT2=FILTER(midT1,MIN(Assignments[Assignment Start Day]))
VAR val =
CALCULATE ( SELECTEDVALUE ( Assignments[Assignment Start Day] ), midT2 )
VAR nam =
CALCULATE ( SELECTEDVALUE ( Assignments[person_name] ), midT2 )
RETURN
IF (
Assignments[person_name] = nam
&& Assignments[Next Assignment] = 0,
val,
BLANK ()
)
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solutions to help the other members find it more quickly.
First off, thank you! This worked for instances where Assignment[person_name] occurs on (2) rows (where a person has 2 assignments). I did have to alter the calculation by removing "<=" and replacing with "=" for the earlier function, after doing this it worked.
AND (
Assignments[person_name] = EARLIER ( Assignments[person_name] ),
Assignments[Next Assignment] = 1
)
But this column calculation is not working with people that have (3) rows/assignments, a current assignment, a next assignment, and another next assignment. See example below:
Hi, @dacosta
You can create the following Calculated columns to get the result you want.
Column 1
Rank by Person name =
RANKX (
FILTER ( ALL ( Assignments ), [person_name] = EARLIER ( [person_name] ) ),
[Assignment Start Day],
,
ASC,
DENSE
)
Column 2
Future Assignment Name =
CALCULATE (
MAX ( 'Assignments'[Assignment Name] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
Column 3
CALCULATE (
MAX ( 'Assignments'[Assignment Start Day] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solutions to help the other members find it more quickly.
This worked perfectly, thanks for the help.
Here's an example based on person and index.
Assignment Table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUfLKz8hTcMlPBTIDivKzUpNLFBxzCjISgXy//LLU3KTUIgVDMx0FIwMjkHLfxKLkDAUjiIAhUCAkKNQVSBkoxepEKxliN9AptQRknn9yST7IOBO4ZpiIkQVYyAgo5OboEwwy0FApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Person Name" = _t, #"Assignment Name" = _t, #"Assignment Start Day" = _t, #"Assignment End Day" = _t, #"Current Assignment" = _t, #"Next Assignment" = _t])
in
Source
Measure (although this can be debated, I think a calculated column would be appropriate too)
User | Count |
---|---|
84 | |
69 | |
69 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |