Creating new columns based on colums from related Table

[Learner86]

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 · Accepted solution]

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

[Learner86]

Many thanks for the proposed solution. There is just one point: How can I combine the two tables by using of the ID-columns?

[tamerj1]

@Learner86 
Can you please provide the expected output? Do you want that using M or DAX?

[Learner86]

@tamerj1: You can find my expected output in my first post. I want to use DAX.

[Arul]

@Learner86 ,

Check this post, it might help you to solve your issue.

https://community.powerbi.com/t5/Desktop/RELATED-function-not-working-in-one-to-many-relationship/m-... 

Since related works from one side of the relationship it won't fulfill your need but you can try related table.

Thanks,

Arul

[Learner86]

@Arul : Thanks for your answer. Unfortunately, the recommended solution doesn't work in my case.