Relationships between tables are being reversed for no clear reason.

[Maracles]

Hi, I'm new to PowerPoint so forgive me if this is a basic question - my 'Learn PowerPivot' book is on the way!

I am trying to establish a relationship between two tables. I have tried to post a picture but do not have permissions so I will try to explain.

Table 1 is called ‘Raw’
Table 2 is called ‘Enquiries’

I want RAW to be the base table, and I want to use the column ‘OrderID’, which is text in the format of ‘ABC12345’, and link it with the equivalent column in Enquiries which is called ‘Coverted Order’ which is again a text format of 'ABC12345’.

My understanding is that if I set-up this relationship, I can then add calculated columns to my RAW table and pull in information from related records in ‘Enquiries’.

So, as an example. If in the RAW table I have an ‘OrderID’ of ‘ABC50000’. I can search the enquiries table for any record that has a matching ‘Converted Order’ value of ‘ABC50000’ and then I can pull in associated values such as the ‘Source’, ‘Date Converted’ and so forth.

If I try and set this up however it does not work. Instead I get an error “The relationship cannot be created in the requested direction” and it reverses the direction of the relationship.

Can someone explain to me why this is happening. Why can I pull in data from the RAW table and associate it with enquiries, but not the other way? I have tried following this page but I have to admit to not understanding although it seems to be the same issue I'm having. Working with relationships in PowerPivot & SSAS BISM Tabular | Javier Guillén

Thanks!
James

 

[miguel.escobar]

It basically means that the table that you thought that didn't have duplicates actually has duplicates, and the one that you thought that didn't have any duplicates actually has duplicates so they need to revert the relationship in order to work. In most cases Powerpivot will not warn you about it and just create the correct relationship for you taking in consideration that it can only handle one-to-many (1-to-n) relationships

Is the relationship working? if not, can you try uploading the workbook so I can take a look at the data within those tables?