Can't change relationship direction

[rjs2]

I need to change he direction so I can do a related in DAX, but it wont let me.  its set to many to one, and I need it one to many.

Everytime I change and swap the top table and the bottom table and click ok, it doesnt change and stay the same direction.

[PC2790 · Accepted solution]

Have you enabled the automatic detection of relationship?

If yes, then Power BI will automatic detect the cardinality according to the data according to unique and non-unique values

Please check if the table pointed towards the 'one' is having unique values and one towards the many might contain duplicate values. That is the criteria of setting up the cardinality.

More about relationships:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

[AlexisOlson · Accepted solution]

It would seem one of your tables has unique values in that column and the other doesn't. So it only makes sense create many-to-one but not one-to-many.

[rjs2]

no duplicates

[dedelman_clng]

Hi @rjs2 - does GrantHistory have one or more rows for a given TGR_SEQ, or does it have exactly the same number of distinct values as rows as you show above for GrantCACT? If there are more rows than unique records in GrantHistory, you cannot reverse the cardinality.

Hope this helps

David

[rjs2]

GrantHistory has more rows, due to the fact, its every status the TGR_SEQ has gone thru.  I am trying to pull user that did the second to last status and I need the full history.

[dedelman_clng · Accepted solution]

Hi @rjs2 - since GrantHistory is the "child", it simply cannot be the one side of a one-to-many.

You should be able to get what you want, it will just take some DAX coding. I would suggest you lay out specifically what you are trying to accomplish, but start it in a new thread. I would look at this post for pointers on what kind of details should be given : How-to-Get-Your-Question-Answered-Quickly 

David

[rjs2]

Gotcha, I have already looked to either do this in query or creative dax.

Thanks

[AlexisOlson]

The cardinality is different from the direction. You can only use one-to-many if the column you're creating a relationship on actually has exactly one row for each distinct column value. If that's not true then, by definition, you cannot create a one-to-many relationship.

Since you're talking about relationship direction, you might try setting the cross filter direction to both.

[rjs2]

cross filter to both wont let me look up related indax

[rjs2]

if that was the case, why can I create many to one?

[AlexisOlson · Accepted solution]

It would seem one of your tables has unique values in that column and the other doesn't. So it only makes sense create many-to-one but not one-to-many.

[PC2790 · Accepted solution]

Have you enabled the automatic detection of relationship?

If yes, then Power BI will automatic detect the cardinality according to the data according to unique and non-unique values

Please check if the table pointed towards the 'one' is having unique values and one towards the many might contain duplicate values. That is the criteria of setting up the cardinality.

More about relationships:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

[rjs2]

again there are no duplicates - there are only 255 records, I am able to inspect it visually and also in excel. 255 unique records.

[amitchandak]

@rjs2 , One side(The table you want) might have a blank value in the joining field or a duplicate value in the joining the field

[rjs2]

No blanks (thats impossible since its a record number) 

And no duplicates, its the side that has one.  Instead of many to one, I want to swap the tables to have it one to many.