Forum Discussion
Bidirectional relationship cardinality issue
Hello everyone,
Help needed! Me and my colleague spent days on this and tried everything but no luck at all.
So our problem is we have a fact table which has unique id. We also have multiple dimension tables which id may repeat.
We defined one to many, single direction relationships with no problem.
Then client wants to pull data from dimension 1 and dimension 2 at the same time.
So we changed single direction relationships to bidirectional. But it still says can't determine the relationship between the two dimension table. Unless we change cardinality to many to many!
But I don't understand why, they are one to many for sure.
Is it some Power BI bug or we missed anything?
Thank you very much!
Hi LingZhu ,
This is a common point of confusion in Power BI, but it's not a bug. The issue you're facing is called relationship ambiguity. When you set both of your one-to-many relationships to be bidirectional, you create two possible filter paths between Dimension 1 and Dimension 2. A filter could travel from Dimension 1 → Fact → Dimension 2, or it could travel the other way. Because the Power BI engine sees two valid routes, it can't determine which one to use, so it returns an error to avoid giving you an unpredictable result.
Changing the cardinality to many-to-many appears to solve the problem because M:M relationships are considered "weak" relationships. They handle ambiguity differently by deferring the filter logic until query time. However, this is not a recommended solution as it can lead to significantly worse performance and potentially incorrect results. You are correct that your underlying data is one-to-many, and your model should reflect that for accuracy and efficiency. Using many-to-many here just masks the real modeling issue.
The best-practice solution is to keep your relationships defined as one-to-many with a single filter direction. When you need to filter one dimension by another for a specific analysis, you temporarily activate bidirectional filtering within a DAX measure using the CROSSFILTER function. For instance, if you wanted to count items in Dimension 2 based on a selection in Dimension 1, you would write a measure like this:
Count of Dim2 filtered by Dim1 = CALCULATE ( COUNT ( 'Dimension 2'[ID] ), CROSSFILTER ( 'Fact'[Dimension1_ID], 'Dimension 1'[ID], Both ) )In this measure, the CALCULATE function modifies the filter context, while CROSSFILTER tells the engine to treat the relationship between 'Dimension 1' and the 'Fact' table as bidirectional (Both) just for this single calculation. This approach gives you the correct result while maintaining a clean, efficient, and unambiguous star schema model.
Best regards,
8 Replies
- amitchandak
Super User
LingZhu , Ideally, the fact should be on the many side. In this case, the dimensions are actually facts. So, in a 1-M relationship, you need to have grouped data from the fact table and ungrouped data from the central table. When you take ungrouped columns from both facts (dimensions, in your case), it will not work. You can only take ungrouped columns from either of the many-side tables, not both, in a 1-M relationship.
You are right! I checked MS learn page about many to many relationship and the sample they use is like you said, the other way around!
- MasonMA
I would suggest having your ETL team redo the data transformations. This doesn't look Star-schema ready:)
For details please refer to Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Hope it helps:)Yes, because this is from SAP Business Object, they have different methodology of creating data model. When moved to Power BI, it does not seem to work.
- tmhalila
Resolver II
Can you share a sample dataset?
Your scenario needs some data modeling practice. No, need to force cardinalities, you will end up struggling with filters and wrong numbers.
You are right, we already have some wrong data. Let me see if I can create a sample dataset, because the real data set has 200 tables and 5000 columns. 😅
- DataNinja777
Hi LingZhu ,
This is a common point of confusion in Power BI, but it's not a bug. The issue you're facing is called relationship ambiguity. When you set both of your one-to-many relationships to be bidirectional, you create two possible filter paths between Dimension 1 and Dimension 2. A filter could travel from Dimension 1 → Fact → Dimension 2, or it could travel the other way. Because the Power BI engine sees two valid routes, it can't determine which one to use, so it returns an error to avoid giving you an unpredictable result.
Changing the cardinality to many-to-many appears to solve the problem because M:M relationships are considered "weak" relationships. They handle ambiguity differently by deferring the filter logic until query time. However, this is not a recommended solution as it can lead to significantly worse performance and potentially incorrect results. You are correct that your underlying data is one-to-many, and your model should reflect that for accuracy and efficiency. Using many-to-many here just masks the real modeling issue.
The best-practice solution is to keep your relationships defined as one-to-many with a single filter direction. When you need to filter one dimension by another for a specific analysis, you temporarily activate bidirectional filtering within a DAX measure using the CROSSFILTER function. For instance, if you wanted to count items in Dimension 2 based on a selection in Dimension 1, you would write a measure like this:
Count of Dim2 filtered by Dim1 = CALCULATE ( COUNT ( 'Dimension 2'[ID] ), CROSSFILTER ( 'Fact'[Dimension1_ID], 'Dimension 1'[ID], Both ) )In this measure, the CALCULATE function modifies the filter context, while CROSSFILTER tells the engine to treat the relationship between 'Dimension 1' and the 'Fact' table as bidirectional (Both) just for this single calculation. This approach gives you the correct result while maintaining a clean, efficient, and unambiguous star schema model.
Best regards,
Thank you! I think this is the only solution in terms of performance and data accuracy. I will discuss with my team.
