How can I force powerpivot relationshipt to be one-to-one?
Actually, I would be satisfied just getting to pick the direction of the one-to-many. I have one table, the result of a SQL query that I want to use as a lookup table. It is unique in its key column, but I don't know how to tell Powerpivot that. I have another table which is linked from Excel where the user can type in a key to look up stuff in the big table. When I use diagram view to connect these tables, it creates a many-to-one relationship in the wrong direction and then RELATED('lookuptable'[key value]) fails. There is really a one-to-one relationship between the one row supplied by the user and some row in the big table. How do I convince PowerPivot of this? The version I am using is 2016.
Answer
You can change which is the lookup/1-side table and which is the many-side, either by clicking and dragging from the many to the one, or by switching the tables in the edit relationship dialog.
Coming eventually for 2016, likely no later than the GA release of SQL Server 2016, is the concept of a bi-directional relationship in which filter will flow both ways between the tables in a relationship, and hopefully many-to-many support natively along with that.
Edit:
In the Create Relationship dialog menu, whichever table you define on the left will become the many side of the relationship, so you can simply create the relationship through this dialog.
That being said, I have confirmed that when defining a relationship in Excel 2016 by clicking and dragging fields, the field that you drag becomes the many side of the relationship, and the field that you drop on becomes the one side of the relationship. The arrow points in the direction that filter context will flow (note that this is the opposite direction of the lookup arrows in previous versions of Power Pivot). Filter context follows the arrow, but calls to RELATED() or RELATEDTABLE() go in the opposite direction of the relationship arrow. You can only call one of the RELATED*() functions from the table that the arrow points to. If you need to reverse the relationship, just delete and redefine it by dragging from the other table.
To modify the relationship in the Edit Relationship dialog menu, you will need at least three tables in the model, and you will then need to juggle the tables, using the third table to allow selection of the first two appropriately.
Edit 2:
It turns out that the edit relationships dialog launched by Relationships in the pivot table Analyze ribbon menu does not behave in the same way as the Power Pivot dialog, and so the following works to switch relationship direction regardless of how many tables are in your model. Table is the many side, Related Lookup Table is the one side.
