cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karthiksunku Regular Visitor
Regular Visitor

How to get a column from table2 to table1 ?

Hi All,

 

i took below data into power bi and it has come many to many relationships on unique id or ch3 status.

 

when i tried to taking the column from 2017 table to 2018 to compare any change in ch3 status to last year and this year using related function it is not allowing me to do.

 

Can someone help me on comparison of change in 2017 to 2018 ch3 status for both the years.

 

Capture.PNGCapture1.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to get a column from table2 to table1 ?

Hi @karthiksunku ,

 

In both 2017 and 2018 tables, for each RCP ACCOUNT NO, there existing multiple records which share the same CH3 STATUS value, right? 

 

If so, you could create a bridge table which lists all available unique Account.

Dim account =
UNION (
    VALUES ( '2017 data'[Account] ),
    EXCEPT ( VALUES ( '2018 data'[Account] ), VALUES ( '2017 data'[Account] ) )
)

1.PNG

 

Create a one to many relationship from Dim account table to 2017 and 2018 data table based on RCP ACCOUONT NO.

2.PNG

 

Add relative fields into table visual, you could rename each field being displayed in column header.

3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: How to get a column from table2 to table1 ?

Hi @karthiksunku ,

 

You need to establish a one to one relationship between 2017 table and 2018 table based on UniqueId field.

1.PNG

 

Create a calculated column in 2018 table.

LY status = RELATED('2017 data'[Ch3 status])

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
karthiksunku Regular Visitor
Regular Visitor

Re: How to get a column from table2 to table1 ?

Hi @v-yulgu-msft ,

 

There are duplicates in Unique ID as well which i can't share all the data file here.

 

when i create a relationship on Unique ID it is coming as MANY TO MANY .... and i guess you know Related  dax function doesn't work on Many to many relationship.

 

Now could you please suggest me how to get a column.

 

Regards,

Karthik S

Community Support Team
Community Support Team

Re: How to get a column from table2 to table1 ?

Hi @karthiksunku ,

 

Since the reationship type is many to many between two tables, how should we determine which record to be returned from 2017 table to 2018 table? For example, there existing multiple records for Id 10001 in both tables, how to match them?

 

Please post some dummy data that can reproduce your scenario and show us your desired output. How to Get Your Question Answered Quickly

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
karthiksunku Regular Visitor
Regular Visitor

Re: How to get a column from table2 to table1 ?

HI @v-yulgu-msft 

 

Please find below the link, you can find the data in the link provided.

 

https://drive.google.com/file/d/1Q9GnkVJJAmt20djYC8YDBIvxeBEfJr-h/view?usp=sharing

 

Also, i need to get the output as below where you can ignore recipient code and take only RCP ACCOUNT NO AND CH3 STATUS.

 

Please do let me know if you need anyother info.

 

Capture.PNG

Community Support Team
Community Support Team

Re: How to get a column from table2 to table1 ?

Hi @karthiksunku ,

 

In both 2017 and 2018 tables, for each RCP ACCOUNT NO, there existing multiple records which share the same CH3 STATUS value, right? 

 

If so, you could create a bridge table which lists all available unique Account.

Dim account =
UNION (
    VALUES ( '2017 data'[Account] ),
    EXCEPT ( VALUES ( '2018 data'[Account] ), VALUES ( '2017 data'[Account] ) )
)

1.PNG

 

Create a one to many relationship from Dim account table to 2017 and 2018 data table based on RCP ACCOUONT NO.

2.PNG

 

Add relative fields into table visual, you could rename each field being displayed in column header.

3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.