Star Schema cross-join

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:

1. You're massively under-utilizing the tool
2. 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

Disappointing, but that's reality sometimes. Thanks.

(maybe someday...)

https://ideas.powerbi.com/forums/265200-power-bi/suggestions/11434623-allow-bi-directional-filtering-for-directquery-rep