Forum Discussion

Prodan's avatar
Prodan
Frequent Visitor
6 years ago

Suspected referential integrity issue

Hi all,

 

I created a new SQL dabatase from scratch with two tables, sales and product, along with its corresponding relationship. There're no referential integrity violation between these tables whatsoever.

 

If I import this database into Power BI desktop using direct query mode and evaluate the product table with VALUES function, it returns an extra blank row. Based on the official documentation, VALUES only returns an extra blank value when a referential integrity violation is present, however, this is not the case. The issue can also be reproduced using the slicer visualization, which executes a VALUES function in the background.

 

 

The only way I found to fix this was to select the "Assume referential integrity" option when setting up the relationship between the tables in Power BI Desktop. Switching to import mode also fixes the issue. As far as I know, these configurations have nothing to do with fixing referential integrity issues but they solved my problem somehow.

Any ideas on why this is happening?

Need Help

4 Replies

    • Prodan's avatar
      Prodan
      Frequent Visitor
      6 years ago

      Hi Greg_Deckler , they are related through the id and product_id column. See image below.

       

       

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Prodan , if there is any product in the table which is missing in product chances are there you can get blank values. Do you have such a case ?

     

    • Prodan's avatar
      Prodan
      Frequent Visitor
      6 years ago

      Hi amitchandak, There's no referential integrity violation between these two tables (if that's what you mean). You can check the content of them in the screenshot from my original message.