Summerizing data over 2 tables

[dabutchart]

I have 2 tables. Table1 has a column StaffNum.

Table2 has columns CourseTitle & StaffNum

Is it possible to create a table where column 1 is all the StaffNums from Table1 and column 2 contains all the courses relating to that StaffNum. For example, the output could be

StaffNum	CourseTitle
123	Course1
123	Course2
123	Course3
123	Course4
234	Course1
345	Course2
345	Course4

[v-henryk-mstf · Accepted solution]

Hi @dabutchart ,

Create the following data model, then refer to the corresponding fields in the table visual and filter the rows that are not empty in the fiter.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

@dabutchart , isn't the table you want (with columns StaffNum and CourseTitle) the same as Table2 that you mention you have (which has columns CourseTitle and StaffNum)?

How is the table you want different to Table2?

[dabutchart]

The difference would be that the output table would contain all StaffNums from Table1 and the CourseTitles from Table2. 

[Anonymous]

@dabutchart , sorry but I cannot see the difference. Could you please post an example showing Table1, Table2, and the desired output table?

[dabutchart]

Table1

StaffNum

123

234

345

Table2

789	Course1
123	Course1
123	Course2
123	Course3
123	Course4
234	Course1
345	Course2
345	Course4

Output

123	Course1
123	Course2
123	Course3
123	Course4
234	Course1
345	Course2
345	Course4

[Anonymous]

@dabutchart , ok so your output should be Table2 excluding any rows where the StaffID does not exist in Table1.

An alternative solution would be to create a calculated table using this DAX expression:

output = 
    CALCULATETABLE(
        'Table2',
        INTERSECT(
            VALUES('Table1'[StaffNum]),
            VALUES('Table2'[StaffNum])
        )
    )