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

IndexPerson NameAssignment NameAssignment Start DayAssignment End DayCurrent AssignmentNext AssignmentFuture Assignment  Name (need help creating this column)

Future Assignment Start Day

(need help creating this column)

0John DoeProject AlphaNovember 16, 2020March 26, 2021TRUE0Project BetaOctober 4, 2021
1John DoeProject BetaOctober 4, 2021October 28, 2022FALSE1(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!
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.

5 Replies

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

     

     

     

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    5 years ago

    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's avatar
      dacosta
      Regular Visitor
      5 years ago

      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's avatar
        v-cazheng-msft
        Community Support
        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.