How to get a column from table2 to table1 ?

[Anonymous]

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.

[v-yulgu-msft]

Hi @Anonymous ,

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

Create a calculated column in 2018 table.

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

Best regards,

Yuliana Gu

[Anonymous]

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

[v-yulgu-msft]

Hi @Anonymous ,

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

[Anonymous]

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.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

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

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

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

Best regards,

Yuliana Gu