Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
no duplicates
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
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.
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
Gotcha, I have already looked to either do this in query or creative dax.
Thanks
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.
cross filter to both wont let me look up related indax
if that was the case, why can I create many to one?
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.
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
again there are no duplicates - there are only 255 records, I am able to inspect it visually and also in excel. 255 unique records.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |