Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Data inconsistency when using Mixed Mode

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...
Need Help
Toerstad's avatar
Toerstad
Advocate I
7 years ago

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

 

RelationshipsImportDualDirectQuery
ImportStrongStrong 
Dual Strong 
DirectQuery StrongStrong

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Toerstad
      Advocate I
      6 years ago

      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.