Forum Discussion

yodha's avatar
yodha
Icon for Helper IV rankHelper IV
6 years ago
Solved

Issues with one to one relationship

Hi,

i have 2 tables, both are coming from different data sources. 

Table 1

ID           Type                                           Date

1            "Type":"A", "Group"="A"              Aug- 16-2020

2            "Type":"A", "Group"="A"              Aug -16-2020

3            "Type":"B", "Group"="B"              Aug -15-2020

4            "Type":"B", "Group"="B"              Aug-15-2020

Table 2 

ID            Type                                            Date

1            "Type":"A", "Group"="A"              Aug- 16-2020

2            "Type":"A", "Group"="A"              Aug -16-2020

3            "Type":"B", "Group"="B"              Aug -15-2020

4            "Type":"B", "Group"="B"               Aug-15-2020

 

If i create relationship between "Table 1- ID" column and  "Table 2 -ID" column, it would be one-to-one,

the relationship between "Table-1 Type" column and "Table 2-Type"coulmn is going to be many-to- many,

and between "Table-1 Date" column and "Table-2 Date" column is again many-to- many.

 

My reqirement is, i want to create a table visual with "Table 1" of "ID", "Type" columns and "Table 2", of "ID", "Type" Columns, and then i should create calculated columns/measures, if "Table 1" of "ID" = "Table 2" of "ID" then true, else false, and if "Table 1" of "Type" of "Group" (should compare substrings)= "Table 2" of "Type" of "Group" then true else false.

 

I am unable to add all the 4 columns to the visual (ID, Type from Table 1 & ID, Type from Table 2), Can anyone help me how to create a relationship between these two tables so that i can add all 4 columns to the table visual, and then create calculated column/measure.

 

Thanks in advance.

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi yodha ,

     

    1. Add custom columns to the two tables respectively.

    2. Spilt Column

    3. Create a calculated column in table1.

    Column = 
IF(
    Table1[ID] = RELATED(Table2[ID]) && Table1[Custom.2] = RELATED(Table2[Custom.2]),
    TRUE(), FALSE()
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • mhossain's avatar
    mhossain
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi yodha 

     

    So you have Table1 and Table2, and [ID] column is unique between the tables.

     

    --Go to PowerQuery window and select Table1

    --in the toolbar click merge, see below pic

    --Now select Table2 in the second table option, and select [ID] in both of the table for join.

    --Ok, this will create a field and you can expand your columns from Table2, and from here you can build custom/calculated fields.

     

    Hope this helps.

     

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

    Hi yodha ,

     

    1. Add custom columns to the two tables respectively.

    2. Spilt Column

    3. Create a calculated column in table1.

    Column = 
IF(
    Table1[ID] = RELATED(Table2[ID]) && Table1[Custom.2] = RELATED(Table2[Custom.2]),
    TRUE(), FALSE()
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.