Forum Discussion
Relationship direction keeps changing itself
Hello,
Not sure if I am missing something simple here or if this a bug that has not been addressed but I have a basic report to make with 2 tables:
Contact table ( a table of contacts and their basic info)
Jobs table (a table of available volunteer jobs, this includes 2 references to contact Ids (Client and Volunteer))
I am trying to make a relationship FROM contacts(Id) TO jobs(Volunteer__c), and powerBI does allow me to do this, however as soon as I exit the relationships window it will swap the FROM and TO around which is causing a headache as I wanted to use the RELATED function to grab only contacts with a related job record.
Not all contacts have a related job record so maybe this is the issue it is facing, if so how can I get all the contacts that have their Id present in the Volunteer__c field in the jobs table?
I found this topic in another thread but it doesnt seem to have an answer and was posted some time ago:
Any assistance is greatly appreciated.
Thanks,
El
Hi Anonymous ,
The Related wotrks from the many to one side so in you case you are making the formula incorrectly. Let's break down the calculation you need.
You want all the Jobs that exist for a contact correct.
If you pick up you contact first you get a single value and then you try on the line to place all the related lines of Job this is not possible because you are getting a multiple value for each line so the result is an error.
If you do the other way around starting from the Jobs you are getting for each line of the job a single line of the Contact so this is possible and returns a value.
Is a question of logic you cannot park 2 cars in a single space (sorry for the comparision).
4 Replies
- MFelix
Super User
Hi Anonymous ,
Is this a one to many relationship?
When you refer that things are getting swapped do you mean that you see the many side on top of the relationship visualization?
I believe this is only a visual bug, because if you look at the bottom where there is the relationship type you should have many to one (so its also inverted) also check if the arrow direction on the relationship view is correct.
If the arrow is correct and the relationship type is also correct based on the order of the tables you are viewing everything is correct. (admit is frustrating the first times because it's not natural)
Hello,
Thank you for the help.
Yes this was a one to many where 1 contact id may be set as a volunteer__c on many jobs.
Getting exactly the same issue as in that other thread I linked:
After making the relationship, before pressing close:
After closing:
Is it at all possible this would affect the DAX? I can't seem to get the RELATED() function to work when using it with something like
table=FILTER(Contact,RELATED('Volunteer Job'[Id])<>'x')but RELATED() will work when I use it the other way around (FROM Job TO Contact) in something like:
table=FILTER(Volunteer Job,RELATED('Contact'[Id])<>'x')Its possible I am just writing this completely wrong though.
I am just trying to achieve a table where I can see all contacts that have job records only.
Thanks,
El
- MFelix
Hi Anonymous ,
The Related wotrks from the many to one side so in you case you are making the formula incorrectly. Let's break down the calculation you need.
You want all the Jobs that exist for a contact correct.
If you pick up you contact first you get a single value and then you try on the line to place all the related lines of Job this is not possible because you are getting a multiple value for each line so the result is an error.
If you do the other way around starting from the Jobs you are getting for each line of the job a single line of the Contact so this is possible and returns a value.
Is a question of logic you cannot park 2 cars in a single space (sorry for the comparision).