Relationship between three tables

[cinthiafkc]

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!!!

[Anonymous · Accepted solution]

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.

[Greg_Deckler]

@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.

[cinthiafkc]

@Greg_Deckler 
The column ID_Entity of Investment Table is the one which contains both ID_city and ID_State