cancel
Showing results 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

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
Super User
``````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 6
Super User
``````Time new =
MAXX (
FILTER ( RELATEDTABLE ( Table2 ), Table2[Status] = "New" ),
Table2[Time]
)``````
``````Time In Progress =
MAXX (
FILTER ( RELATEDTABLE ( Table2 ), Table2[Status] = "In Progress" ),
Table2[Time]
)``````
Helper I

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

Super User

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

Helper I

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

Super User

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

Thanks,

Arul

Proud to be a Super User!

Helper I

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

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors