Forum Discussion

fchukoskie's avatar
fchukoskie
Advocate I
10 years ago
Solved

Star Schema cross-join

I'm creating my first PowerBI dashboard, using DirectQuery against an Azure SQL Data Warehouse instance, and am seeing strange cross-joins.   When I add 1 fact table and 2 dimensions to the model a...
  • andre's avatar
    andre
    10 years ago

    i don't think bidirectional will work with direct query mode

fchukoskie's avatar
fchukoskie
Advocate I
10 years ago

It's not Azure. Here's a sample matrix created from a DirectQuery against AdventureWorksDW.

 

Count (Distinct) of FactInternetSales.CustomerKey:

 

Count (Distinct) of DimCustomer.CustomerKey:

Here's the model:

  • greggyb's avatar
    greggyb
    Resident Rockstar
    10 years ago

    Perform your counts on the fact table, or make sure that every relationship in the chain between two tables has the relationship cross filter direction set to 'Bi-directional'

     

     

    • fchukoskie's avatar
      fchukoskie
      Advocate I
      10 years ago

      Using the key field as an example was contrived. I really want to attributes from the dimension table in a star schema. If we can't, we'll just have to start authoring views to flatten everything (at which point, we probably pick a different tool).

       

      As to the filter, how do I enable that for tweaking so that we can pick "bi-directional"?

      • andre's avatar
        andre
        Memorable Member
        10 years ago

        i don't think bidirectional will work with direct query mode