Suspected referential integrity issue

[Prodan]

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?

[amitchandak]

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

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.

[Greg_Deckler]

@Prodan - How are those tables related? On what columns?

[Prodan]

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