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.
In your example the tables are related with the unique ID table that you created. But they are no where related to each other. If you use date from one table then the other table won't recognize it. If both are fact table then I don't see any reason to relate them using ID. In your case you should just add a date table and relate them both with the date table, that way you can use the date from the date table and get the counts from both the table.
DatawithDinesh just tried:
Third table is now referring to dates:
Model relations:
Now I see the counts of ID for OSP are recognized by the date (thank you!) but they are not recognizing the division per Citycode (always the same value). Even though Citycode is present in both the tables.
Should I add relations also for Citycode then? That means a fourth 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?
