Creating new column based on foreign key

Question

Hi!&nbsp;I have the following problem. I have 3 tables.&nbsp;SCHOOLS TABLEID | Name | Address&nbsp;COURSES TABLEID | Name | School_ID&nbsp;CHILDREN TABLEID | Name | Course_ID&nbsp;How can I add to the CHILDREN TABLE the School_ID? In other words i need to create a column which looks for the children COURSE_ID, go to the courses table and look for that id and retreive the SCHOOL_ID and add that value to the children table.Any help would be great!&nbsp; Thanks in advance

icey · Accepted Answer

Hi&nbsp;vpirozzi3&nbsp;,There are two situations:1. If no ‘Many-to-Many’ relationship among the three tables, you can use ‘Related’ to add a ‘SchoolID’ column in table ‘Children’.SchoolID = RELATED('Courses 2'[School_ID])&nbsp;2.&nbsp;If with ‘Many-to-Many’ relationship among the three tables, you need to create another table.Table 1 =
FILTER (
    CROSSJOIN (
        'Childen 1',
        SELECTCOLUMNS (
            'Courses 1',
            "CourseID", 'Courses 1'[ID],
            "SchoolID", 'Courses 1'[School_ID]
        )
    ),
    [CourseID] = 'Childen 1'[Course_ID]
)&nbsp;This is my PBIX file.&nbsp;Best Regards,Icey&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

Forum Discussion

Creating new column based on foreign key

2 Replies

Related content

Create columns group

Creating a date column

Creating a custom column

unpivoting columns and creating datetime column

create custom column concatenated