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.

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.

      • dacosta's avatar
        dacosta
        Regular Visitor
        5 years ago

        This worked perfectly, thanks for the help.