Forum Discussion
Relationship between three tables
Hi everyone!
I have a Fact table with a column which contain a foreign key.
This column may contain IDs that references to the Dimension City Table (whose DID countains 7 digits) or to the State Dimension Table (ID has 2 digits).
Someone could help on how should I make the relationship between the Fact Table and the Dimensions one in this case?
Thank you!!!
Hi cinthiafkc ,
Please follow these steps:
1. Add a Custom column based on the length of the ID_Entity column:
=if Text.Length( Number.ToText( [ID_Entity]) )=2 then "ID_State" else "ID_City"2.Click Custom column-->Pivot columns-->use ID_Entity as Values
After applying these changes, the relationships would be automatically built:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi cinthiafkc ,
Please follow these steps:
1. Add a Custom column based on the length of the ID_Entity column:
=if Text.Length( Number.ToText( [ID_Entity]) )=2 then "ID_State" else "ID_City"2.Click Custom column-->Pivot columns-->use ID_Entity as Values
After applying these changes, the relationships would be automatically built:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.cinthiafkc Can you provide some example data? Seems like you should split that column into two maybe. Either that or perhaps combine your city and state dimensions. Hard to be sure with the information provided.
Greg_Deckler
The column ID_Entity of Investment Table is the one which contains both ID_city and ID_State
