Forum Discussion
Cannot validate relationship for tables in direct query mode
I am asked to change the storage mode for certain tables from import to direct query mode. I have to delete them and rebuild from data sources with direct query. However, after rebuilding I encounter errors that power BI cannot validate the relationship. Morevoer, the visuals do not work any more. If I switch back to import mode, the relationship is validated fine and visuals are back working. Please help.
11 Replies
- mdaatifraza5556
Super User
Hi Dragut
One of the reasons may be the Data Type of the Relationships.Go to Power Query ---> Select the columns used in relationships ---> Ensure they have the same Data Type in both tables.
If this answers your questions, kindly accept it as a solution.
If you found this helpfull then give kudos.All of them are of text data type and I have made sure there is no null or missing values in them. But the relationship could not be validated and visuals don't work.
- pankajnamekar25
Ensure Both Tables Use DirectQuery Make sure that both tables in the relationship are in DirectQuery mode. Mixing Import and DirectQuery can cause validation issues.
Validate Key Integrity Confirm that the join keys (especially on the “one” side) are unique and not transformed in a way that interferes with the relationship.
Review Relationship Cardinality Double-check that the relationship’s cardinality (e.g., one-to-many) and direction are correctly set and supported by your data source.
Avoid Unsupported Transformations: DirectQuery mode has limitations. Ensure that any calculated columns or transformations on the key columns are supported in DirectQuery.
Data Source Capabilities Some data sources have restrictions on join operations. Verify that your data source supports the type of relationship you’re creating
Thanks,
PankajIf this solution helps, please accept it and give a kudos, it would be greatly appreciated.
All checked fine. But still do not work. My colleague has tried saveing to pbip and modified it there and eventually got it to work. The problem is I cannot reproduce that method. It still does not work on my version even after modifying the pbib files.
v-aatheequeCommunity Support
Hi Dragut
Thanks for reaching out to Microsoft Fabric Community Forum.You can go from direct query to import in Power BI but from Import to direct query it might be challenge.
You cannot do that directly because many features in import mode are not supported in direct query mode.However you can follow some steps mentioned in
https://spgeeks.devoworx.com/switch-import-to-directquery-mode-powerbi/
to resolve the issue.Additionally, you can even visit this link to get more clarity.
https://community.fabric.microsoft.com/t5/Desktop/How-to-convert-a-power-bi-report-from-import-to-Direct-query/m-p/1141395#M518517Hope this helps!
Regards,
v-aatheequeIf this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
I reckon it's the meta data behind the scene in Power BI models that caused this issue.
