Apology:
I must admit that in the last two years I’ve told many Power BI/Kusto customers not to worry about relationships that are created as M:M.
I was pretty sure that with Direct Query, such relationships are fine,
Indeed, the generated queries looked fine and performed as expected.
I recently became aware that the number of queries generated for some visuals e.g. Matrix and tables can be affected by the type of relationships between the participating tables.
Creating a relationship between two queries from Kusto
Usually, the dialog of creating a relationship will look like this:
Even thou the ProductKey in the products table is unique, PBI “thinks” that it isn’t because the result of counting the unique number of keys is not accurate (see dcount() (aggregation function) - Azure Data Explorer & Real-Time Analytics | Microsoft Learn)
At this point, you should change the Cross filter direction setting, and the final dialog will look like this
Why this can be a problem?
For some visuals, PBI needs to issue multiple direct queries to calculate all the values.
In a Matrix with multiple fields on rows, each level in the nesting will need to be calculated in a different query.
In the examples I included you can see 6 queries sent for each refresh of a single visual.
Unless
PBI can sometimes calculate the totals by adding up the values from the more detailed level.
This is true for measures that only include aggregations like sum or count and calculations between such aggregations.
In the attached examples the measures Sales,Cost and Profit% are such measures and so the number of queries can be much lower.
The measure Products# uses DISTINCTCOUNT and so totals cannot be calculated without running a separate query against the source for each level.
Unless
When a M:M relationship exists between the fact and the tables used on rows, the PBI engine can’t be sure that even a simple(additive) measure can be calculated without a separate query for each level.
In these cases, the number of queries is high regardless.
How to force the relationship to become 1:M
There are multiple ways to change the relationship but the easiest one is from the properties pane:
Don’t forget to set the Assume referential integrity to yes and to click Apply changes.
The referential integrity setting will ensure that the generated joins will be of the kind – inner.
Attached examples
There are two examples using the same data and the same measures.
The only differences are the relationships.
Using the performance analyzer, you can refresh the visuals and see the difference.
With M:M the two matrices require the same number of queries.
With 1:M the matrix that does not include the distinctcount measure refresh much faster and sends less queries.