Forum Discussion
Manage relationship - Power Bi
Hi, I am having a trouble in choosing "many to one" cardinality between two tables. I have a master dataset and two sub dataset, I wanted to connect "Many to one" cardinality from master dataset to both sub datasets. But it's not accepting the relationship by throwing a note stating that "It creates Ambiguity between two sub datasets". It still connects with one sub dataset perfectly, but not for two datasets parallely. It will be much useful if you guys help me sort this out. TIA
Hi MonishaSarangan ,
Please check that your tables meet the criteria for creating relationships.
Let me give you an example, if you need to join in a many-to-one way, then your child table relationship column needs to be unique, and the tube series of the main table can have duplicate values.
As you can see, the Year column and the ID column are unique in subtables.
Reference: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
In Power BI, you cannot create a "Many to one" relationship from one table (the master dataset) to two other tables (sub datasets) directly because it would lead to ambiguity. This is because Power BI wouldn't know which one of the two relationships to use when resolving queries involving both sub datasets.
However, there are a few alternative approaches you can consider based on your specific needs:
**1. Separate Relationships**: If you need to connect the master dataset to both sub datasets independently, you can create separate relationships. For example, you can create one relationship between the master dataset and the first sub dataset and another relationship between the master dataset and the second sub dataset. This avoids ambiguity, but you'll need to be explicit in your calculations and queries to specify which relationship to use.
**2. Use a Bridge Table**: If the relationships between the datasets are many-to-many, consider using a bridge or junction table to connect them. The bridge table will contain unique combinations of keys from both the master dataset and the sub datasets. Then, you can create "Many to one" relationships from the bridge table to the master dataset and each sub dataset.
**3. Data Modeling Restructuring**: If possible, consider restructuring your data to eliminate the need for multiple relationships. This could involve consolidating or transforming your data to have a single connection point between the master dataset and the sub datasets.
**4. Role-Playing Dimensions**: If the issue is related to handling dates (e.g., a date dimension connected to two fact tables), you can create role-playing dimensions. In this approach, you duplicate the date dimension table, give each copy a unique name (e.g., Date and ShipDate), and then create separate relationships to each fact table. This allows you to use the same dimension table for different purposes without ambiguity.
The approach you choose depends on your specific data model and requirements. Please provide more details if you need further assistance with your specific scenario.
Please share data sets if issue is not solved.
Hi MonishaSarangan ,
Please check that your tables meet the criteria for creating relationships.
Let me give you an example, if you need to join in a many-to-one way, then your child table relationship column needs to be unique, and the tube series of the main table can have duplicate values.
As you can see, the Year column and the ID column are unique in subtables.
Reference: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
