Data inconsistency when using Mixed Mode

[Anonymous]

I have following two fact tables -

Table 1 - fact_sale_disk;Location - Table is on a network drive;Mode - Import Mode

sale_id,sale_amount,region
1,200,US
2,200,Britain
3,400,Canada

Table 2 - fact_sale_datalake;Location - Table is on Impala Database queried via view;Mode - Direct Query

sale_id,sale_amount,region
1,200,US
2,200,Britain
3,400,Canada

Here is the dimension table which is in import mode and the source file is on the disk.

Table 3 - dim_region

region,region_description
US, United States of America
Canada,Canada

The Power BI file has these three tables (table_1;table_3 - import mode) and table_2 in direct query mode. I then created relationship between both the fact tables and the dimension table using the region as common attribute.

In a tab, I dragged in a table with table_1 and table_3 attributes. Here is the result shown -

sale_id,sale_amount,region,region_dim
1,200,US,US
2,200,Britain,
3,400,Canada,Canada

However, when I drag in the table_2 (DL one) and table_3 attributes, here is the result shown -

sale_id,sale_amount,region,region_dim
1,200,US,US
3,400,Canada,Canada

Basically in the DirectQuery mode, it is filtering out the keys where there is no match. I want to prevent this happening i.e. exhibit the same behavior as above. Is there a fix for this? How can we workaround the problem?

I am using Power BI Desktop Jul'19 version.

[Toerstad]

Adding to my previous reply.

The cause of the changed behavior seems to be due to a weak relationship between your fact and dimension tables. A relationship between an import and a DIrectQuery table. This won’t allow the join logic to be pushed to the source - and therefore you don't get any unmatched results. By changing storage mode for the dimension table to dual you should get the same behavior as you experienced previously.

You can read more on storage modes here: https://docs.microsoft.com/en-us/power-bi/desktop-storage-mode

Relationships	Import	Dual	DirectQuery
Import	Strong	Strong
Dual		Strong
DirectQuery		Strong	Strong

[Anonymous]

Hi @Toerstad ,

Thanks for the reply. The dimension and fact tables are by nature designed that way i.e. there would be many instances of dimension keys not being in the fact table as the month on month's data gets ingested. I would need to seek out with the business to see to have a all the entries entered into the dimension tables as and when new ones appear.

The other method of relatedtable or lookup dax formulae would definitely degrade performance. 

Coming to the storage mode, I am not able to select properties for individual tables. All I get is PowerBI showing 'Storage Mode:Mixed(Click to Change)' at the bottom. When I do that, it asks if all tables storage mode to be changed to 'Import Mode'.

[Toerstad]

Hi.

Add your dimension table in DirectQuery storage mode.

Then you can change it to Dual storage mode as shown below.

I'm not sure, but since this is a fairly new feature you might have to update your Power BI Desktop.

[Toerstad]

Hi.

I believe this this works as intended, as you usually want the fact-table to be filtered with your dimension tables. And you want your Direct Query to be as fast as possible. A best practice in this scenario is to always keep your dimension table accurate and up-to-date. A fix could be to create your dimension-table based on a SELECT DISTINCT from your fact_table regions.

In cases as this, it is possible to work around this issue by adding measures that looks up the region_dim, and region_description, and then use these in your table. This would affect performance and is not recommended if you got a big dataset (fact_table). Update your dimension-tables instead.

Work-around 

Create measures to look up the values:

Region dim = LOOKUPVALUE('Table3'[region];'Table3'[region],FIRSTNONBLANK('table2'[region],"")) 

Region description = LOOKUPVALUE('Table3'[region_description],'Table3'[region],FIRSTNONBLANK('table2'[region],""))

Create a table-visual to get the desired result:

Create a table-visual with the following columns and measures

Table2[sales_id],
Table2[sale_amount],
Table2[region],
[Region dim],
[Region description]

... and you should be good to go.