Visual shows blank row when in Direct Query mode

[TonyJordan]

Hi all,

I have the model as shown below with a fact table and a dimension table, both in Direct Query mode, and I have the same tables but then in Import mode to show you a difference that has come up. 

What happens is that when I create a visual, like the table visual, and I select only the values from the dimension table and I select 'Show items with no data', it shows a blank value in the Direct Query visual and it doesn't do that for the Import visual. I did not expect that to happen. Why does it show the blank value for the Direct Query model?

Please note that it doesn't do this for the fact table with the 'Show items with no data' option selected.

When I delete the relationship between the Direct Query tables it does not show this blank row anymore so I guess it has something to do with the relationship. But I don't understand it.

Please note that in my database, both the fact table and the dimension table have no blank or NULL values. Also, I only have 3 values in my fact table and they all correspond to a value in my dimension table. I also traced the SQL query Power BI sends to my database for the Direct Query visual and the result of this query also shows no blank or NULL values.

I hope someone can clarify this for me.

Kind regards.

[marcorusso · Accepted solution]

It is the expected behavior. If you don't include a column of the fact table, the only way to avoid the blank row is to join the fact table - which could be expensive. So the assumption is that there could be a blank row if you didn't specify "assume referential integrity" - checking the presence of the blank for every query on the dimension would be extremely expensive. DirectQuery is already slow enough this way.

[TonyJordan]

Hi Marco, thank your for your reply.

I will consider it behavior by design then.

I have to note that the blank row does not appear when I remove the relationship between the dual storage mode dimension and the Import storage mode fact table. But doing so (and altering my model consequently) would defeat the purpose of the Dual storage mode I guess.

So the other remaining options are not using the option 'Show items with no data' or accepting that a blank row appears when using it.

[TonyJordan]

Hi, thank you for your reply.

So I checked what happens when I would add fields from the fact table to my table visual. So interestingly the blank row still appears when I add the fact table value column, but it disappears when I also add the dimension key column of the fact table. See below images.

I checked the DAX queries for the import and direct query visuals and they are both the same. So I don't know about the Auto-Exist issue..

What I also found was that when I choose the option 'Assume referential integrity' in the relationship properties, the blank row also disappears. However, this does not solve the issue when Dual mode tables are used (which is the case for me). So I updated the model with a Dual mode table to show you what happens.

Note that for both relationships going to the Direct Query fact table, the 'assume referential integrity' option has been chosen.

Now I maybe am even more confused about this behavior.

[lbendlin]

This is exciting.  I would have not expected the queries to have the same query plan.  Maybe time for @marcorusso to have a look at this.

[lbendlin]

Does it happen when you only select fields from the dimension tables?

Smells like "auto-exist" might be involved. Understanding DAX Auto-Exist - SQLBI

Have a look at the different query plans that are created by the visuals.