Remove duplicates from a calculated column and use as a unique key

[Anonymous]

Hi

I have 2 tables:

The first is enrolment that has a list of subjects the learners are enrolled to. There is column named Enrolment [Reference] that can either be the programme or element enrolment depending on the funding stream. The unique field is the enrolment ref and student ref that I concatenate together.  

The second is PBI_WBI_Tutors which contains 4 columns a list of the programmes (reference 1), the corresponding element enrolments (Reference  2), the student ref and tutors against them.

In order to link the correct tutor to the enrolment I have to select the correct reference number from the PBI_WBI_tutors table and create a concatenated field that matched the enrolment table. I have created the following formula to do this:  

Overall Reference =

IF( LOOKUPVALUE( Enrolment[Reference], PBI_WBL_Tutors[Reference1], PBI_WBL_Tutors[Reference1]) <> BLANK(),

PBI_WBL_Tutors[Reference1] & PBI_WBL_Tutors[StudentReference],

PBI_WBL_Tutors[Reference2] & PBI_WBL_Tutors[StudentReference]).

I want to use this column as the unique key to create a relationship to the enrolments table. I am having numeorus problems.

The formula wont work unless there is an exisitng relationship between the tables. I have created a fake relationship and removed some data to test this and the formula does work. However when I try to change the relationship to the new one I get one of 2 errors

The overall reference (column I have created with the above formula) is not unique so is there a way to remove duplicates from this column?

When manually removing further data from the column I tried to change the fake relationship I have created to the one I want (formula above) and it errors saying there is a circular dependancy.

Is it possible to achieve what I want?

Thanks

[Anonymous]

Does the enrollment and Tutors has Many to Many relationship?

Thanks

Raj

[Anonymous]

I want the relationship to be one to many which is why I want to remove the duplicated values from the tutors table. 

Thanks 

[Anonymous · Accepted solution]

Yes i undersand that. I am trying to confirm the existing relationship between these tables.

If M-M is the relationship then you may need to consider creating bridge table/ relationship table , which is explained in the below URL:

https://community.powerbi.com/t5/Report-Server/Many-to-Many-Relationship-between-tables-and-show-dat...

Hope this helps.

Thanks

Raj