Forum Discussion
Advocate IStar Schema cross-join
i don't think bidirectional will work with direct query mode
Advocate IUsing 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"?
Memorable Memberi don't think bidirectional will work with direct query mode
- greggyb
Resident Rockstar
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:
- You're massively under-utilizing the tool
- You'll need to include, at minimum, a COUNTROWS( '<fact>' ) in the matrix / table to force the dimensions to play nicely together with relationships to the fact table
- fchukoskie
Disappointing, but that's reality sometimes. Thanks.
(maybe someday...)
- greggyb
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).
- fchukoskie
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.
- andre
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.
