Weird issue with Dual storage mode (bug?)

[SoltV]

I noticed a strange behavior when using Direct Query with SQL Server and Dual Storage mode.

When a table contains a text-field that a has value with additional space at the end of it, Dual Storage mode can't handle it correctly. In Direct Query mode everything works normally.

The issue is visible in the latest version of PowerBI Desktop (July 2019) and PowerBI.com (I used Azure DB, but the same happens with local SQL Server). I originally tested and confirmed this issue with Power BI Desktop June 2019 version.

Steps to reproduce the problem:
- download the zip-file here: https://www.dropbox.com/s/7ccveo4dbf30bpu/PowerBI_dualmode.zip?dl=0
Zip file contains the pbix-file and the necessary tables and data needed for it.
- run the sql-script in Sql Server (to an empty database or some testing database). Script contains both the necessary schema and data for this example
- open up the pbix-file and update the connection to your Sql Server
- change the storage mode for the dimTable between Direct Query and Dual modes and watch the effect on the report. Upper table shows only one row when Dual mode is selected  (should be two rows).
- to fix the issue, remove the extra space from the Name-field in dimTable (in SQL Server) for DimensionKey = 2
- Refresh the data in report. Now the table on the top always shows two rows, no matter which storage mode you select. This is the expected behavior.

This is weird because on the report, there's another table as well. It's working normally all the time because it doesn't have the Name-field in it.

I know this seems to be an edge case (needs Direct Query & Dual modes + the measures need to be written in a certain way), but this was something that took hours to figure out.

[v-yuta-msft]

@SoltV ,

I have made a test on your issue. But the result is different from your description. In dual storage mode, the handle is normal(The two rows can be viewed) like below:

 

However in direct query mode, the data can't be seen.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SoltV]

Hi @v-yuta-msft 

What you saw is true, but the problem is visible on the report. Sorry for using the term table like this, I meant table-visual (the problem is visible in other visuals as well, I used the table-visual in this example).

On the report itself, there are two table-visuals. The top one shows only one row when dimTable is in Dual Mode:

But when changed to Direct Query, the same table-visual contains two rows (this is what the table-visual should always look like):