Bidirectional relationship cardinality issue

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,