Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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