Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX for creating column based on other column - different time points

I am trying to create a new column (Variable 2) based off of the ID, Time point and Variable 1 in the table below. I want to create a new column (Variable 2) which copies the value of Variable 1 at Time point 0 for each ID. So I would want my resulting table to look like this:

 

ID

Time point

Variable 1

Variable 2

1

0

0

0

1

1

1

0

1

2

0

0

1

3

2

0

2

0

1

1

2

1

1

1

2

2

0

1

2

3

1

1

3

0

0

0

3

1

0

0

3

2

0

0

3

3

0

0

4

0

2

2

4

1

1

2

4

2

0

2

4

3

1

2

 

Does anyone know what the DAX would be to create this column

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a calcualted column

    Column =
CALCULATE (
    MAX ( 'Table'[Variable 1] ),
    FILTER (
        'Table',
        'Table'[ID] = EARLIER ( 'Table'[ID] )
            && 'Table'[Time point] = 0
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

     

    The easiest way I know to do this is to add a conditional column in query editor as below. Assuming you have only one '0' value for "Time Point".....

     

    Then select the column "Variable 2", go to Transform->Fill(drop down)-> Down

     

    If this helps, mark it as a solution.

    Kudos are nice too.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your help. I don't think this is doing what I had wanted it to. For each ID, I want Variable 2 to equal [Variable 1 at Time point 0] (see the example table i provided).

      • VasTg's avatar
        VasTg
        Icon for Memorable Member rankMemorable Member
        6 years ago

        Anonymous 

         

        The conditional column populates Variable 2 when Time Point = 0. The output will looks like below..

         

         

        Did you do the next step?

         

        "select the column "Variable 2", go to Transform->Fill(drop down)-> Down"

         

         

        If this helps, mark it as a solution.

        Kudos are nice too.

         

         

         

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    Create a calcualted column

    Column =
CALCULATE (
    MAX ( 'Table'[Variable 1] ),
    FILTER (
        'Table',
        'Table'[ID] = EARLIER ( 'Table'[ID] )
            && 'Table'[Time point] = 0
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.