cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Learner86
Helper I
Helper I

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. 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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

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

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

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

Arul
Super User
Super User

@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

Arul

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors