Hi @gautampruthi
This is a great question. The primary reason for the difference in cardinality detection between Import and Direct Query modes lies in how Power BI interacts with the data source and the data itself in each mode.
Import Mode: In Import mode, Power BI copies data from the data source into its own in-memory engine. This allows Power BI to analyze the entire dataset directly and determine relationships, including cardinality, based on the data it has imported. Since Power BI has full visibility of the dataset, it can accurately determine the best cardinality for relationships.
Direct Query Mode: In Direct Query mode, Power BI does not import data. Instead, it queries the data source in real-time as needed. Because of this, Power BI relies on the metadata and schema of the data source to determine relationships. It does not analyze the actual data to determine cardinality. This can lead to differences in how relationships are detected compared to Import mode. Additionally, Direct Query mode may impose certain limitations based on the data source's capabilities and constraints, which can further affect relationship detection.
However, you can always modify or create a relationship manually to make the relationships more appropriate for your model.
For more detailed information on how to create and manage relationships in Power BI, I recommend reviewing the following documentation: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
