I am struggling to set up a measure in Excel to look up a text value from a dimension table based on a key in a fact table. I am using Power Query to import tables from a variety of data sources as connections, so there is no literal table in the spreadsheet.
My fact table is CourseCompletions and contains completion data for training courses: the columns include EmployeeID, CourseID, and DateCompleted.
My dimension table is EmployeeData and contains columns like EmployeeID, EmployeeFirstName, EmployeeLastName, and EmployeeEmail.
I have set up a relationship between the tables on EmployeeID. The fact table is on the many side of the relationship, and the dimension table is on the one side.
My goal is to have a pivot table with a list of the EmployeeIDs, a sum of the courses completed for each employee, and the email address for the employee. (It's a little more complicated than that, but that simplified version will illustrate the issue. I will have a VBA routine that loops through the rows of the pivot table and sends an email to each employee with a count of how many courses they have completed, which is why I need the email address).
The measure that I attempted to create is in the fact table, and is =RELATED(EmployeeData[EmployeeEmail]). However, I get the error that the column doesn't exist or doesn't have a relation to any table in the current context. I have double checked that there are no duplicates for EmployeeID in the EmployeeData table, that the EmployeeData table is on the one side of the relationship, and that the relationship is active.
My fact table is CourseCompletions and contains completion data for training courses: the columns include EmployeeID, CourseID, and DateCompleted.
My dimension table is EmployeeData and contains columns like EmployeeID, EmployeeFirstName, EmployeeLastName, and EmployeeEmail.
I have set up a relationship between the tables on EmployeeID. The fact table is on the many side of the relationship, and the dimension table is on the one side.
My goal is to have a pivot table with a list of the EmployeeIDs, a sum of the courses completed for each employee, and the email address for the employee. (It's a little more complicated than that, but that simplified version will illustrate the issue. I will have a VBA routine that loops through the rows of the pivot table and sends an email to each employee with a count of how many courses they have completed, which is why I need the email address).
The measure that I attempted to create is in the fact table, and is =RELATED(EmployeeData[EmployeeEmail]). However, I get the error that the column doesn't exist or doesn't have a relation to any table in the current context. I have double checked that there are no duplicates for EmployeeID in the EmployeeData table, that the EmployeeData table is on the one side of the relationship, and that the relationship is active.
