Creating relationships in Power Pivot

[dicken]

HI, recently could not get the relationship form date to fact table in the right direction , i had several tables with date columns and had created a calendar, but the direction was always wrong,
so I then tried joining to one of the other tables this worked fine, so I deleted from data model
all but fact table, created a new calendar table and the direction was fine, I then had to put the other table back, unlike power bi, there does not seem to be any direction options in the editing dialog box, does anyone know of a way to set the direction, other than just dragging and hoping it gets it right.

Richard.

 

[Matt Allington]

Here’s the thing. If you join 2 tables, and both have unique values, power pivot cannot tell which is on the many side and which is on the “one” side. If you join 2 tables, one has unique values and the other has repeating values, it will always get it right.

If you create a 1 to many relationship in power pivot, and actually both tables are unique p, you can double click and change the direction

 

[dicken]

Here’s the thing. If you join 2 tables, and both have unique values, power pivot cannot tell which is on the many side and which is on the “one” side. If you join 2 tables, one has unique values and the other has repeating values, it will always get it right.

If you create a 1 to many relationship in power pivot, and actually both tables are unique p, you can double click and change the direction

Yes I realized the problem re one to one, but if I understand you, the direction is down to the order the tables are selected?
which seems to be counter intuitive, as the first ends ends up on the many side, is this correct?

Richard.

 

[Matt Allington]

It's not that simple and it is different in Power Pivot for Excel and Power BI.

Excel

• If both tables[column] have unique values in both columns, then a 1 to many relationship will be created and the direction of the relationship depends on which table you start with. I don't remember the default. If you want to change the direction, you can change ie.
• If one table[column] has repeating values and the other table[column] has unique values, then a 1 to many relationship will be generated in the same direction regardless of which table you start from.

Power BI

• If both tables[column] have unique values in both columns, then a 1 to 1 relationship with bi directional cross filtering will be created. You can manually change it to 1 to many in either direction and would normally turn off bi directional filtering.
• If one table[column] has repeating values and the other table[column] has unique values, then a 1 to many relationship will be generated in the same direction regardless of which table you start from.