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'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 =
CONVERT (
LOOKUPVALUE (
'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]
),
STRING
)
RETURN
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.
Solved! Go to Solution.
@ld17 , Change this like
Table 2 = CROSSJOIN ( Distinct ('Names and Job Titles'[Associate.title]), Distinct ( 'Course List'[Title]))
and try
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@ld17 , Change this like
Table 2 = CROSSJOIN ( Distinct ('Names and Job Titles'[Associate.title]), Distinct ( 'Course List'[Title]))
and try
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thank you SO much! That works perfectly.
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |