Reference Another Row for Future Assignment

[dacosta]

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)

Future Assignment Name =

if(and(

'Assignments'[Next Assignment]=1, 'Assignments'[person_name]=MAX(Assignments[person_name]), var projectname='Assignments'[assignments.project_name])

return(projectname), BLANK())

This equation isn't working, I know there is something wrong but I cannot figure it out. Can anyone please help!

[v-cazheng-msft]

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.

[dacosta]

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:

[v-cazheng-msft · Accepted 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.

[dacosta]

This worked perfectly, thanks for the help.

[lbendlin]

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)

Future Assignment Name =

var i=SELECTEDVALUE('Assignment Table'[Index])

var p=SELECTEDVALUE('Assignment Table'[Person Name])

var fi=MINX(Filter(All('Assignment Table'),'Assignment Table'[Index]>i && 'Assignment Table'[Person Name]=p),'Assignment Table'[Index])

return MINX(Filter(All('Assignment Table'),'Assignment Table'[Index]=fi && 'Assignment Table'[Person Name]=p),'Assignment Table'[Assignment Name])