topic Re: Creating new columns based on colums from related Table in DAX Commands and Tips

Creating new columns based on colums from related Table

Learner86 — Fri, 20 May 2022 12:26:01 GMT

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:

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.

Re: Creating new columns based on colums from related Table

Arul — Fri, 20 May 2022 13:09:13 GMT

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-p/609220

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

Thanks,

Arul

Re: Creating new columns based on colums from related Table

Learner86 — Fri, 20 May 2022 13:19:52 GMT

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

Re: Creating new columns based on colums from related Table

tamerj1 — Fri, 20 May 2022 13:28:56 GMT

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

Re: Creating new columns based on colums from related Table

Learner86 — Mon, 23 May 2022 05:49:55 GMT

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

Re: Creating new columns based on colums from related Table

tamerj1 — Mon, 23 May 2022 06:07:51 GMT

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

Re: Creating new columns based on colums from related Table

Learner86 — Mon, 23 May 2022 07:52:49 GMT

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