Issues with one to one relationship

[yodha]

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.

[v-lionel-msft · Accepted solution]

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.

[mhossain]

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.