Forum Discussion
Relationship between two tables with duplicates IDs
Yes you can create a City Code table and relate it with the two tables. But I would suggest first you examine your requirement on what fields would be slicing/filtering the two tables and then create a dimension table having all the unique values. For example you can create a unique table having ID, Citycode, State etc. and relate the table with ID to both the tables. Now when you slice and dice use the fields that is available in the new table.
Yes you can create a City Code table and relate it with the two tables. But I would suggest first you examine your requirement on what fields would be slicing/filtering the two tables and then create a dimension table having all the unique values. For example you can create a unique table having ID, Citycode, State etc. and relate the table with ID to both the tables. Now when you slice and dice use the fields that is available in the new table.
Thank you for the reply. I created the extra tables to create relationships: date, citygroup and ID. It is indeed working! (for the moment ID relationship is not needed, but I already created it)
As you have seen I created separate calculated tables. I understood I cannot group calculated tables even though it would be the most easy-to-read solution. So I think you propose to use a Composite Key with one only bridge table?
Yes. It would be better if you can create a single table with a Primary Key, City, state etc and then relate this table to other two tables using the primary key. This way you will reduce your model size and also you should be able to handle the data easily.