Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating a new column by combining two different columns from different tables

I have three different tables - Master, Servers and Workstations.    They all contain the same columns but Server and Workstation are filtered for Device Type = Server or Workstation. The tables, S...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    If you have the "one to many" relationship  from master tables to the others, we can try to create a calculated column using following formula:

     

    Status =
"Server: " & CONCATENATEX ( RELATEDTABLE ( 'Server' ), [Status], "," )
    & UNICHAR ( 10 ) & "Workstation: "
    & CONCATENATEX ( RELATEDTABLE ( 'Workstation' ), [Status], "," )

     

    If they have the one to one relationship, we can use the following formula to create a calculated column:

    Status =
"Server: " & RELATED ( 'Server'[Status] )
    & UNICHAR ( 10 ) & "Workstation: "
    & RELATED ( 'Workstation'[Status] )

     


    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


    Best regards,

     

     

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

Just create relationships Master - Servers and Master - Workstaions (both one-to-many) and add columns to visual

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks! I created the relationship but  the column that should have the combined values from Server and Workstation is not doing so?

 

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    what do you mean "combine"? how it should look like? give an example please

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    If you have the "one to many" relationship  from master tables to the others, we can try to create a calculated column using following formula:

     

    Status =
"Server: " & CONCATENATEX ( RELATEDTABLE ( 'Server' ), [Status], "," )
    & UNICHAR ( 10 ) & "Workstation: "
    & CONCATENATEX ( RELATEDTABLE ( 'Workstation' ), [Status], "," )

     

    If they have the one to one relationship, we can use the following formula to create a calculated column:

    Status =
"Server: " & RELATED ( 'Server'[Status] )
    & UNICHAR ( 10 ) & "Workstation: "
    & RELATED ( 'Workstation'[Status] )

     


    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


    Best regards,