I'm working on a project that required me to cross join two tables ("Names and Job Titles" and "Course List") to form a new table called "Table 2."
From there, I created a calculated column called "Completion Date" because I needed all users to be correlated with all training names. This has worked fine, but I am now getting a circular dependency error when I try to create a relationship between Table 2 and Names and Job Titles. How do I fix this?
I need to join Table 2 back to Names and Job Titles because I want the user's name that is located in Table 2 to be joined to their job title and department.
The DAX expression I used to join the table is as follows:
Table 2 = CROSSJOIN ( VALUES ('Names and Job Titles'[Associate.title]), VALUES ( 'Course List'[Title]))
And then the calculated measure I used for Completion Date is as follows:
Completion Date = VAR vResult =
'In-Classroom Training Log'[Date],
'In-Classroom Training Log'[Attendee.title], 'Table 2'[Associate.title],
'In-Classroom Training Log'[Name of Classroom Training.lookupValue], 'Table 2'[Title]
IF ( ISBLANK ( vResult ), "Not Completed", vResult)
The result will be a matrix with the department name, job title, and name that looks like the following. Right now, however, I can only get my name column to show up since it isn't allowing me to join Table 2 back to Names and Job Titles, which is where department and job title are located.
After some reasearch, it seems that I need to adjust my calculated measure, but I cannot figure out how to do so, given that I am not a DAX expert.
Alternatively, I believe I could cross-join departments and job titles with my names and training titles, and I understand how to do this. However, I cannot figure out how to then get the correct name with the correct department and job title.