Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paulalmond91
Helper II
Helper II

Relationships on Multiple Calculated Columns - Calculated Table Solution Help

I have a worksheet where I have a set number of activities. Against these activities, there is a calculated column that does a lookup to return an ID to find the latest assessor and latest employer. I then want to run a relationship between this calculated column to the respective tables so I can use the fields in the other tables. The issue I have is I can only have 1 active relationship between the calculated columns otherwise it generates a circular dependency. I think I need to create a calculated table with a unique index to run the relationship but I am unsure how to do this - can anyone help?

 

Theres are my calculated columns which work:

ActAssessorID = CALCULATE(MAX('Assigned Assessors'[Assessor ID]),FILTER('Assigned Assessors',AND('Assigned Assessors'[Active Assessor]=true(),'Assigned Assessors'[Trainee ID]='Activities'[Trainee ID])))
ActEmployerID = CALCULATE(MAX('Assigned Employers'[Employer ID]),FILTER('Assigned Employers',AND('Assigned Employers'[Active Employer]=true(),'Assigned Employers'[Trainee ID]='Activities'[Trainee ID])))


Here is the data:

Activity Table

Trainee IDActivity TypeActAssessordIDActEmployerID
0295-0003-324786Tech Cert0295-0003-2511970295-0003-202732
0295-0003-331846ESW0295-0003-2514390295-0003-256636
0295-0003-305494ESW0295-0003-2512290295-0003-259772
0295-0003-322177ESW0295-0003-2512850295-0003-262829
0295-0003-305494ESW0295-0003-2512290295-0003-259772
0295-0003-322177ESW0295-0003-2512850295-0003-262829
0295-0003-305300ESW0295-0003-2508050295-0003-256528
0295-0003-305300ESW0295-0003-2508050295-0003-256528
0295-0003-305300ESW0295-0003-2508050295-0003-256528

 

Assessor Table

Assessor IDAssessor
0295-0003-250805Assessor A
0295-0003-251195Assessor B

 

Employer Table

Employer IDEmployer
0295-0003-256528Employer A
0295-0003-259772Employer B
1 ACCEPTED SOLUTION

@jdbuchanan71 Had a bit of a revelation and realised the solution was quite simple. I was returning the ID value then trying to create a relationship on that ID.

I've changed the calculated columns to return the actual name of hte assessor or employer (which is the information I want to visually display) and removed the relationships and now this works perfectly.

 

Thanks for your help though!

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

Hello @paulalmond91 

It is hard to advise without seeing the uniqueness and fileds of the actual tables.  Could you possibley share your .pbix file by uploading it to OneDrive or DropBox and sharing the link?

@jdbuchanan71  

Thanks for helping here.

Here is a pbix file with the desired outcomes and the relationships in place for what I want to achieve.

https://www.dropbox.com/s/qvkulv25exgv0fj/Testing.pbix?dl=0

I haven't included my full file as its huge and contains some confidential info etc.

 

Basically, the values you see in ActEmployerID and ActAssessorID are the correct result of the calculated column expressions included in the original post and are working as expected its just I cant create more than one relationship from a calculated column on the Activities table and I need both to have a relationship as it is in the pbix file. Currently I have to choose either assessor or employer.

 

Does this help?

@paulalmond91 

Your sample file doesn't align with the calculated columns formulas you listed.  I'm assuming the tables names 

are

Assessors = Assigned Assessors

Employers = Assigned Employers

 

But you list several fields in your calculated columns formula that are not in the Assessors table

 

ActAssessorID =
CALCULATE (
    MAX ( 'Assigned Assessors'[Assessor ID] ),
    FILTER (
        'Assigned Assessors',
        AND (
            'Assigned Assessors'[Active Assessor] TRUE (),
            'Assigned Assessors'[Trainee ID] = 'Activities'[Trainee ID]
        )
    )
)

@jdbuchanan71 Had a bit of a revelation and realised the solution was quite simple. I was returning the ID value then trying to create a relationship on that ID.

I've changed the calculated columns to return the actual name of hte assessor or employer (which is the information I want to visually display) and removed the relationships and now this works perfectly.

 

Thanks for your help though!

Nice!  Glad you were able to figure it out and thank you for letting me know you got it solved.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.