Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |