Forum Discussion

dacosta's avatar
dacosta
Regular Visitor
5 years ago
Solved

Reference Another Row for Future Assignment

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 e...
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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.

lbendlin's avatar
lbendlin
Super User
5 years ago

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])