Forum Discussion

Dragut's avatar
Dragut
Frequent Visitor
1 year ago

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...
Need Help
pankajnamekar25's avatar
pankajnamekar25
Icon for Super User rankSuper User
1 year ago

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's avatar
Dragut
Frequent Visitor
1 year ago

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's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    1 year ago

    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#M518517

     

    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's avatar
      Dragut
      Frequent Visitor
      1 year ago

      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's avatar
        v-aatheeque
        Icon for Community Support rankCommunity Support
        1 year ago

        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!