Cannot validate relationship for tables in direct query mode

[Dragut]

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.

[Dragut]

I reckon it's the meta data behind the scene in Power BI models that caused this issue.

[v-sdhruv]

Hi @Dragut ,
I wanted to check if you had the opportunity to review the information provided by the users. . If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you

[v-sdhruv]

Hi @Dragut ,
I wanted to check if you had the opportunity to review the information provided by the users. . If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you

[v-sdhruv]

Hi @Dragut ,
I wanted to check if you had the opportunity to review the information provided by the users. . If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you

[pankajnamekar25]

@Dragut 

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,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[Dragut]

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-aatheeque]

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-Di...

Hope this helps!
Regards,
v-aatheeque

If 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!

[Dragut]

I have two versions. table a to table b is import-import and the relationship validation works. If it is import-direct query, the relationship validation fails. Same data set. Different result.

[v-aatheeque]

Hi @Dragut ,

Thanks for the clarification.

You can perform certain checks in this case apart from insuring both tables follow same data type when forming a relationship.

1.Check the text for white space, trim the column.

2.Direct Query mode has some limitations on the features it can support, especially when it comes to relationships. 
In some cases, features like bi-directional filtering or certain DAX expressions may not work when one of the tables is in Direct Query mode. Check if your relationship involves such unsupported features.

Additionally, you can visit this link where the issue was addressed earlier.

Solved: Relation between Direct Query Table and Import tab... - Microsoft Fabric Community

Hope this helps!
Regards,
v-aatheeque

If 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!

[mdaatifraza5556]

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.

[Dragut]

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.