Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yodha
Helper IV
Helper IV

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.

 

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

Hi @yodha ,

 

1. Add custom columns to the two tables respectively.

v-lionel-msft_1-1597820476960.png

2. Spilt Column

v-lionel-msft_0-1597817341154.png

3. Create a calculated column in table1.

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

v-lionel-msft_2-1597820590693.png

 

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.

View solution in original post

2 REPLIES 2
v-lionel-msft
Community Support
Community Support

Hi @yodha ,

 

1. Add custom columns to the two tables respectively.

v-lionel-msft_1-1597820476960.png

2. Spilt Column

v-lionel-msft_0-1597817341154.png

3. Create a calculated column in table1.

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

v-lionel-msft_2-1597820590693.png

 

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
Solution Sage
Solution Sage

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

mhossain_0-1597777138214.png

--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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.