Is the relation "single" between 2 table the same as "LEFT JOIN" in SQL?

[Anonymous]

Hello 😊 

When I want to use a left join's behavior (get all values even when there's no match between them) I use the relation "single" and when I want to get only the values that match together I use the relation "both".

But is this correct way to use "single" and "both", what is the exact difference between them ? 

I didn't find any useful answer in the net, so that's why I ask this question here.

[negi007 · Accepted solution]

@Anonymous  dont get confuse between relatinship between tables and the direction of relationship

Cardinality

The Cardinality option can have one of the following settings:

Many to one (*:1): A many-to-one relationship is the most common, default type of relationship. It means the column in a given table can have more than one instance of a value, and the other related table, often know as the lookup table, has only one instance of a value.

One to one (1:1): In a one-to-one relationship, the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.

One to many (1:*): In a one-to-many relationship, the column in one table has only one instance of a particular value, and the other related table can have more than one instance of a value.

Many to many (*:*): With composite models, you can establish a many-to-many relationship between tables, which removes requirements for unique values in tables. It also removes previous workarounds, such as introducing new tables only to establish relationships. For more information, see Relationships with a many-many cardinality.

Cross filter direction

Each model relationship must be defined with a cross filter direction. Your selection determines the direction(s) that filters will propagate. The possible cross filter options are dependent on the cardinality type.

CROSS FILTER DIRECTION

Cardinality type	Cross filter options
One-to-many (or Many-to-one)	Single Both
One-to-one	Both
Many-to-many	Single (Table1 to Table2) Single (Table2 to Table1) Both

Single cross filter direction means "single direction", and Both means "both directions". A relationship that filters in both directions is commonly described as bi-directional.

For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional). For One-to-one relationships, the cross filter direction is always from both tables. Lastly, for the Many-to-many relationships, cross filter direction can be from either one of the tables, or from both tables. Notice that when the cardinality type includes a "one" side, that filters will always propagate from that side.

When the cross filter direction is set to Both, an additional property is available. It can apply bi-directional filtering when row-level security (RLS) rules are enforced. For more information about RLS, see the Row-level security (RLS) with Power BI Desktop article.

Modifying the relationship cross filter direction—including the disabling of filter propagation—can also be done by a model calculation. It's achieved by using the CROSSFILTER DAX function.

Bi-directional relationships can impact negatively on performance. Further, attempting to configure a bi-directional relationship could result in ambiguous filter propagation paths. In this case, Power BI Desktop may fail to commit the relationship change and will alert you with an error message. Sometimes, however, Power BI Desktop may allow you to define ambiguous relationship paths between tables. Precedence rules that affect ambiguity detection and path resolution are described later in this article in the Precedence rules topic.

We recommend using bi-directional filtering only as needed. For more information, see Bi-directional relationship guidance.

links for detailed understanding

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

[Anonymous]

Thank you! Very helpful answer