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?
