Error creating relationship with ADX table in PowerBI modeling— duplicate values on “one” side

Thank you, rohit1991 for your response.

Hi MiluMaria,

Thankyou for the followup.

Based on my understanding, even if the dimension table contains unique keys in ADX, Power BI requires one side of a relationship to be guaranteed unique under all possible query and filter contexts. Since ADX does not expose enforced primary key constraints, Power BI cannot guarantee this at query time and, therefore, blocks a one-to-many relationship. This can surface after model recreation or metadata refresh when cardinality is revalidated. Replicating the table in Power BI does not alter this behavior because the validation still occurs against ADX at query execution.

The possible solution is to adopt a composite or hybrid model by creating a distinct dimension (one row per key) and loading it in Import mode, while keeping the fact table in DirectQuery. This pattern ensures stable relationships. If Import is not feasible, a many-to-many relationship with appropriate DAX adjustments can be used, keeping in view performance considerations.

Additionally, please refer to the links provided below:
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft Learn
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Composite model guidance in Power BI Desktop - Power BI | Microsoft Learn
Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn

We hope the information herein helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.