Forum Discussion
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 and test counts in a simple matrix, creating a "Count Distinct" measure on one of my key fields (a join key between the Fact & Dimension table) works as expected (though slowly) if I pull that field onto the matrix from the Fact table. So, it should be asking SQL for a distinct count of "fact.userid".
However I pull that same field onto the matrix from the Dimension table (dimension.userid), the Count Distinct looks like it's cross-joining to the other dimensions in the matrix (in this case, "Month" from the calendar dimension). Subsequent tests then show that any field from that dimension also cross-joins in the matrix.
The relationships to each dimension show as "Active" under the "Manage Relationships" table, but I can't figure out if there's a way to see the SQL that PowerBI sends to Azure in its DirectQuery to understand why it's counting incorrectly.
Am I handling the relationships wrong, or does PowerBI expect the model to be more "flat" for DirectQuery to work?
i don't think bidirectional will work with direct query mode
11 Replies
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:
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'
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"?
