Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors