PL-300: mastering relationships, cardinality and filter direction

I will teach a practical skill for the PL-300: creating and managing relationships between tables in Power BI — including cardinality, filter direction and when to use inactive relationships. This capability is crucial for the exam and in practice because it ensures that DAX calculations and visuals display correct results.

What you need to know

A data model in Power BI links tables through relationships. Each relationship has at least three important properties:

• Cardinality: One-to-many (1:*), One-to-one (1:1) or Many-to-many (*:*). Cardinality defines how rows correspond between tables.
• Cross-filter direction: Single or Both. Decides how filters flow between tables and affect measures/visuals.
• Active / Inactive: Only one relationship between two tables can be active; inactive relationships can be used temporarily with DAX (e.g.: USERELATIONSHIP).

Simple example: a Sales table with CustomerID and a Customers table with CustomerID. The typical scenario is a 1 (Customers) → * (Sales) relationship, cross-filter direction Single (from Customers to Sales). If there is also a Dates table, you link Dates[Date] to Sales[OrderDate] with a 1:* relationship and usually mark Dates as "Marked as date table" for time intelligence.

How it works / In practice

Here is a practical step-by-step in Power BI Desktop to create and adjust relationships correctly:

1. Import the tables: bring in the Sales, Customers and Dates tables (you can use the Contoso example file or the sample database).

2. Open "Model" (Model view) and drag the key column (for example Customers[CustomerID]) to Sales[CustomerID] to create the relationship automatically.

3. Double-click the relationship line and set the Cardinality (usually One-to-many) and the Cross-filter direction (Single is the safe default). If you have a dimension and a fact table, use Single from the dimension side to the fact.

4. If you need to filter in both directions (for example for dimension tables that interact directly), consider Both carefully — it can solve scenarios but also cause ambiguities and performance impact.

5. If there are two possible relationships between the same tables (e.g.: Sales has OrderDate and ShipDate linked to Dates), leave one relationship active (e.g.: OrderDate) and create the other as inactive. To use the inactive relationship in a calculation, use USERELATIONSHIP in DAX.

Example of a DAX measure that temporarily activates an inactive relationship between Sales[ShipDate] and Dates[Date]:

TotalSalesByShipDate =
CALCULATE(
  SUM(Sales[SalesAmount]),
  USERELATIONSHIP(Dates[Date], Sales[ShipDate])
)

Another useful function is CROSSFILTER, which changes the filter direction between two tables within a calculation:

MeasureBothDir =
CALCULATE(
  SUM(Sales[SalesAmount]),
  CROSSFILTER(Customers[CustomerID], Sales[CustomerID], Both)
)

Note: using CROSSFILTER or setting relationships to "Both" can increase processing cost; use only when necessary for the report logic.

Common mistakes

• Wrong cardinality: setting Many-to-many when a unique key exists causes unexpected calculations. Always check that the column on the "one" side has unique values.
• Indiscriminate Both directions: enabling Both without understanding the filter flow can lead to ambiguity and incorrect results. Only use Both when you understand the consequences in the model.
• Not using inactive relationships correctly: trying to represent two date contexts with two active relationships between Sales and Dates causes an error; instead, keep one active and use USERELATIONSHIP for the other.

How to practice

Practice with real data: import a sales dataset (Sales), customers (Customers) and calendar (Dates) and build scenarios that require alternative relationships (OrderDate vs ShipDate). Create measures that use USERELATIONSHIP and compare results with active relationships. Also test scenarios with dimension tables related to each other (e.g., Products and Categories) to understand when Both is necessary.

For PL-300 preparation, use the official Microsoft Practice Assessment (it's free) to evaluate where you are weakest. Also consult the official Microsoft study guide for PL-300 (free) — both provide guidance on the skills measured. These resources are the right source for practice and study planning.

In summary

• Relationships define how filters and aggregations flow in the model — configure cardinality and direction intentionally.
• Use inactive relationships + USERELATIONSHIP to switch between contexts (e.g.: OrderDate vs ShipDate).
• Avoid using Both indiscriminately; it can solve problems but also introduce ambiguities and performance costs.
• Practice with the sample dataset and use the official Practice Assessment and study guide from Microsoft to guide your preparation.