Forum Discussion

Learner86's avatar
Learner86
Icon for Helper I rankHelper I
4 years ago
Solved

Creating new columns based on colums from related Table

Dear community, 

 

I want to create new columns based on other columns from another table using IF-condition. I hope you can help me. Image I have the following two tables

 

Table1: 

 

ID  

1

2

3

 

Table2:

 

ID    Status            Time

1     New                 5

1     In progress     10

2     New                 7

2    In Progress      12

3    New                15

3    In Progress      20

 

I want to add new colums to the table1. Table1 should like this: 

 

ID      Time new   Time in progress

1        5                10

2        7                12

3       15               20

 

There is a n:1-relation between the two tables. 

 

How can I create the columns Time new and Time in progress? 

 

Thank you very much and kind regards. 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Learner86 

    Time new =
MAXX (
    FILTER ( RELATEDTABLE ( Table2 ), Table2[Status] = "New" ),
    Table2[Time]
)
    Time In Progress =
MAXX (
    FILTER ( RELATEDTABLE ( Table2 ), Table2[Status] = "In Progress" ),
    Table2[Time]
)

6 Replies