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?
Solved! Go to Solution.
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:
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"?
Ah, I did not realize that the cross-filter direction is not an option for direct query.
With single direction relationships, everything is a pseudo-left join from the many side of the relationship to the one side. There's no way to force this to behave as an inner join.
For display purposes, the query generated is a cross-join of all tables whose fields are included. Each row of the resulting table has the measure(s) in the visualization evaluated for the combination of values in that row. Rows which have a blank after evaluating the measure (either because the combination doesn't exist in the fact or a conditional returned blank) are then filtered out of the display.
This is modeled on the behavior of Excel pivot tables and SSAS Multidimensional.
To force the relationship to be evaluated between two tables you must evaluate a measure. In a star schema, that measure must be filtered by the fact table.
If you want to write a measure based on something in the dimension, well then that's not a dimension, strictly speaking. Nonetheless dimension row counts make a great denominator for lots of measures, so we'd usually like to do this.
I'm not sure about the subset of DAX allowed in direct query mode, but I know it's an area still getting attention.
The following DAX utilizes cross table filtering to force the dimension to be evaluated in the context of the fact table:
DimensionRowCount = CALCULATE( COUNTROWS( '<dimension>' ) ,'<fact>' )
This is the same format as the N:N workaround in SSAS Tabular < 2016, which makes sense, because a star schema is essentially a bunch of N:N relationships between dimensions with the fact as a gigantic bridge table.
If you just want to use Power BI as a tabular reporting interface:
Disappointing, but that's reality sometimes. Thanks.
What is your specific reporting need where you need to have matrices without measures based on your fact? There might be some convenient workarounds similar to my post above to cover those.
If it's all tabular reporting without hitting the fact table, then Power BI is not the tool for you. Power BI is specifically optimized for analytical reporting, and not as a fancy UI for a SQL query generator (though Power Query can function as such).
Well, direct query mode has intrinsic technical challenges since the data do not live in the model. I will gladly trade directional relationships for ability to enhance direct query mode based models with calculations.
Fair question on requirements, but I'm really making this up as I go. We're still getting data cleansed & standardized in an Azure SQL Data Warehouse, so I'm trying to evaluate options on BI & Reporting tools in parallel as time allows...which means that there was no requirement for matrices of data. It was just what I was doing that day while exploring options for self-service BI that might mimic what we could get from ROLAP tools like MicroStrategy or Cognos.