Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
@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?
The difference would be that the output table would contain all StaffNums from Table1 and the CourseTitles from Table2.
@dabutchart , sorry but I cannot see the difference. Could you please post an example showing Table1, Table2, and the desired output table?
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 |
@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])
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |