PL-300 exam domain 2 (Prepare and Model the Data) demands that you connect scattered data tables so they work as a single, sensible story. Without relationships and calculated columns, your reports would be like a shuffled deck of cards — all the information is there, but it tells you nothing. Mastering these two tools is what turns raw data into answers that make your boss nod approvingly.
Jump to a section
A simple way to picture Relationships and Calculated Columns
Have you ever tried to find a friend's house using just their name, but every street has a 'Maple Drive'? That is the mess a database without relationships finds itself in.
Imagine you live on a street where every house has a unique number, but you only know your neighbours by their first names. To send a letter to your friend Sarah at number 42, you need a cross-reference: a little address book that says 'Sarah lives at 42'. In Power BI, a 'relationship' is exactly that address book. It connects the real-world identifier (the house number, like a CustomerID) across two separate tables (your street roster and your address book). Now, if you want to calculate the total value of all the mail Sarah received last month, you do not need to rewrite her name into every piece of mail — the relationship does the linking for you. A 'calculated column' is like adding a note in the margin of your address book that says 'Sarah's house colour is blue' based on her address. You are deriving new information from data already in the book, rather than walking the street and writing down colours by hand. Together, they turn a pile of disconnected lists (table A: names, table B: addresses, table C: mail items) into a single, useful directory.
At its heart, Power BI is a storytelling machine for data. But the data rarely arrives in one neat table. It comes in pieces — one table with customer names, another with order dates, and a third with product prices. To tell a coherent story (like 'which customers bought the most expensive products last quarter'), you must first connect those pieces. That is where relationships come in, and calculated columns let you enrich the story with new details.
Let us define the terms from the ground up. A 'table' in Power BI is like a spreadsheet tab — a grid of rows and columns. Each row is a record (one customer, one sale), and each column is a field (name, date, price). A 'relationship' is a logical link between two tables based on a common column. For example, a 'Customers' table and an 'Orders' table both have a 'CustomerID' column. The relationship tells Power BI: 'The CustomerID in the Orders table refers to the CustomerID in the Customers table, just like the house number on a letter refers to a physical house.' This linkage allows you to build a single visual — say, a bar chart showing total sales per customer — without manually merging the two tables.
There are two main types of relationships: one-to-many and one-to-one. The most common by far is one-to-many (1:*). In a one-to-many relationship, one row in the first table (the 'one' side, like 'Customers') can match many rows in the second table (the 'many' side, like 'Orders'). One customer can place many orders, but each order belongs to only one customer. A one-to-one relationship (1:1) is rarer and means that each row in one table matches exactly one row in the other — think of a table of employee badge numbers matched one-to-one with a table of employee personal details. A many-to-many relationship (*:*) is possible but more complex and usually requires a special bridge table; it is often best to simplify your data model to avoid them.
When you drag a field from one table into a visual alongside a field from another table, the relationship automatically dictates how they combine. If the relationship is set up correctly, a filter applied to one table cascades to the other. For instance, if you slice by 'Product Category' in the 'Products' table, the relationship ensures that only sales for those categories appear in your 'Sales' chart. This is called 'filter propagation'.
Now, a 'calculated column' is different from a relationship. It is a new column you add to an existing table using DAX (Data Analysis Expressions) — Power BI's formula language. DAX is a collection of functions and operators similar to Excel formulas, but designed for working with relational data. You use a calculated column when you need to compute a value for each row based on other columns in the same row or from related tables. For example, if you have a 'Sales' table with 'Quantity' and 'UnitPrice' columns, you could create a calculated column called 'TotalPrice' with the DAX formula: 'TotalPrice = Sales[Quantity] * Sales[UnitPrice]'. This formula runs once (when the data is loaded) and stores the result in the new column.
Why use a calculated column instead of a measure? A measure is another type of calculation in DAX, but it is evaluated dynamically as you interact with a report (e.g., summing up totals for a filter context). A calculated column is static per row — it adds a permanent new column to your table. You would choose a calculated column when you need the value to exist as a column for use in relationships, as a row-level filter, or as part of a slicer. For example, if you want to create a relationship between two tables based on a derived field (like the first letter of a customer's last name), you would use a calculated column to create that derived field.
Together, relationships and calculated columns form the backbone of your 'data model'. A data model is simply the organised collection of tables and their relationships. A well-built data model is like a tidy toolbox where every tool has its place — you can grab what you need quickly without confusion. In Power BI, your data model determines how fast your reports run, how accurate your numbers are, and how easily you can answer business questions.
A common beginner question: 'Why not just merge all tables into one giant table?' You could, but that leads to 'data duplication' — repeating the same product name for every single sale, which makes your file huge and slow. Relationships keep your data 'normalised', meaning each piece of data lives in exactly one place (like a product name only in the Products table), saving memory and reducing mistakes.
In summary, relationships connect tables logically without copying data, and calculated columns extend the data you already have with new, row-level information. Both are essential tools for turning raw data into a clean, efficient, and queryable model.
Identify common keys across tables
Start by examining your tables to find columns that logically connect them, like CustomerID, ProductID, or OrderDate. These keys are the foundation for relationships. If no obvious key exists, you may need to create one via a calculated column (e.g., combining FirstName and LastName). This step matters because relationship creation requires consistent data types and values in both tables.
Drag and drop to create relationships in Model view
In Power BI Desktop, go to the Model view. Drag the key column from the lookup table (e.g., 'Customers' table's CustomerID) onto the matching column in the data table (e.g., 'Sales' table's CustomerID). Power BI automatically detects the cardinality (usually 1:*). Review and confirm this is correct. This step is visual and fast, but misclicking can create a ghost relationship that breaks filtering.
Configure the relationship properties
Double-click the relationship line to open properties. Verify: Cardinality (one-to-many, one-to-one), Cross filter direction (single or both), and 'Make this relationship active' checkbox. For standard scenarios, use single direction from the lookup table (the 'one' side) to the data table (the 'many' side). This step prevents filter ambiguity later.
Add calculated columns to enrich the model
Decide if you need a static column based on existing data. Go to the Table tab in Power Query, or use the 'New Column' button in the Report view. Write a DAX formula like: 'FullName = Customers[FirstName] & " " & Customers[LastName]'. This column is now part of the table and can be used in slicers, visuals, or as a basis for relationships. This step is optional but powerful for deriving new categories or labels.
Test relationships with a simple visual
Drag a field from the lookup table (e.g., Customer Name) and a numeric field from the data table (e.g., Total Sales) onto a table visual. Apply a filter from the lookup table (e.g., a slicer on City). Observe whether the numbers update correctly. If totals are unexpectedly repeated or missing, check: are the relationship columns duplicated? Is the filter direction set correctly? Testing at this stage catches errors before building complex reports.
Optimise the data model by evaluating active vs inactive relationships
If you have multiple relationships between the same two tables (e.g., OrderDate and ShipDate both linking to a Dates table), decide which relationship to keep as active. The active one (solid line) will be used by default for all filtering. The inactive one (dashed line) can be activated on demand using USERELATIONSHIP in a measure. This step ensures that you can filter by both date fields without causing ambiguity in default behaviour.
Imagine you work as a junior data analyst for a retail chain called 'ShopSmart'. Your manager asks for a report showing: 'Total revenue by store location for the last month, including a column that flags stores with revenue below the average.' Let us walk through what you actually do, step by step.
First, you open Power BI Desktop. You have three raw tables imported from the company's database: 'Stores' (with StoreID, StoreName, City, Region), 'Sales' (with SaleID, StoreID, SaleDate, ItemPrice, Quantity), and 'Dates' (with Date, MonthName, Year). None of these tables are connected yet. Your first job is to create relationships. - You look at 'Stores' and 'Sales'. Both have a column called 'StoreID'. You drag a line from 'StoreID' in 'Stores' to 'StoreID' in 'Sales' in the Model view. Power BI automatically detects this as a one-to-many relationship (one store, many sales). You confirm the 'Cross filter direction' is set to 'Single' (from Stores to Sales), which means filtering by store will filter sales, but not vice versa. This is the standard pattern. - Next, you connect 'Sales' to 'Dates' using the 'SaleDate' column in Sales and the 'Date' column in the Dates table. Again, one-to-many: one date, many sales. You set the filter direction from Dates to Sales so you can filter sales by date range.
Now you need to create the calculated column. You want a flag that says 'Below Average' for stores whose total monthly revenue is less than the average across all stores. But a calculated column works row-by-row on a single table. You cannot compute a 'total per store' in a calculated column in the Stores table because that total involves many rows from the Sales table. This is a typical situation where a 'measure' would be more appropriate than a calculated column. However, for demonstration, imagine your manager insists on having a static column. You could create a calculated column using the DAX formula: 'BelowAverageFlag = IF(RELATED(Stores[StoreID]) = BLANK(), BLANK(), IF(SUMX(FILTER(Sales, Sales[StoreID] = Stores[StoreID]), Sales[Quantity] * Sales[ItemPrice]) < AVERAGEX(ALL(Stores), SUMX(FILTER(Sales, Sales[StoreID] = Stores[StoreID]), Sales[Quantity] * Sales[ItemPrice])), "Below Average", "At or Above Average")).' But this is complex and inefficient; in practice, you would use a measure instead. The lesson is: know when a calculated column is the right tool (static row-level data) and when a measure is better (aggregations that change with filters).
Finally, you build a report. You drag 'StoreName' from Stores onto a table visual, then 'SaleDate' from Dates, and then 'Quantity' and 'ItemPrice' from Sales. Because the relationships exist, the visual automatically shows the correct numbers: for each store and date, it sums the sales. You add a slicer on 'City' from the Stores table, and the whole report responds, filtering all visuals. Your manager is happy because the data makes sense and responds smoothly to filters.
Key actions an IT professional does daily: checking relationship cardinality (is it one-to-many?), validating filter direction (does it go the right way?), and deciding whether a calculated column or a measure is the correct approach for a business rule.
The PL-300 exam tests your understanding of relationships and calculated columns in several specific ways. You must be ready for multiple-choice, multiple-select, and case study questions that drop you into a data modelling scenario. Here is exactly what they will ask and how to avoid the traps.
First, you must know the three cardinalities: one-to-many (1:*), one-to-one (1:1), and many-to-many (*:*). The exam loves to test whether you can correctly identify the cardinality based on sample data. Trap: they might show you a table of Customers and a table of Orders where one customer appears multiple times in Orders. The correct cardinality is one-to-many (Customers to Orders). A common wrong answer is 'one-to-one' because beginners think 'one customer equals one order' - but the data shows otherwise.
Filter direction is another favourite. They will ask: 'If a user slices by Store, which tables get filtered?' The rule is: filter direction goes from the 'one' side to the 'many' side in a one-to-many relationship. Trap: they might show a situation where you need bidirectional cross-filtering (both directions) for a specific scenario, but the default in Power BI is single direction. The exam expects you to recognise when bidirectional is necessary (e.g., when you have a bridge table that sits between two tables and you need to filter both ways).
Calculated columns: the exam tests your ability to write simple DAX formulas. You do not need to memorise every function, but you must understand common ones like IF, SWITCH, RELATED, and basic arithmetic. A classic question: 'You have a Sales table and a Products table. You want to create a column in Sales that shows the product category name. Which DAX function do you use?' Answer: RELATED. The RELATED function pulls a value from the 'one' side table into the 'many' side table when a relationship exists. Trap: they might offer 'LOOKUPVALUE' as a distractor - but LOOKUPVALUE works even without a relationship, which is less efficient. The exam prefers the relationship-based approach.
Another key exam topic: 'normalisation' vs 'denormalisation'. They might ask: 'Why would you avoid creating a single flat table with all columns?' The correct answer is to reduce data redundancy and improve performance. They may present a scenario where a table has repeated product names and ask you to identify the problem (duplication).
Finally, the exam tests your understanding of 'active' vs 'inactive' relationships. A relationship can be 'active' (the one Power BI uses by default for filtering) or 'inactive' (an alternative route you activate using the USERELATIONSHIP function in DAX). A typical question: 'You have two date columns in the Sales table - OrderDate and ShipDate. You have a Dates table. How do you create two relationships?' Answer: one active (usually OrderDate) and one inactive (ShipDate). They will ask how to use the ShipDate dimension in a visual, which requires USERELATIONSHIP in a measure.
Key definitions to memorise: cardinality, cross filter direction, active vs inactive relationship, calculated column vs measure, RELATED vs LOOKUPVALUE. Trap patterns: mistaking one-to-one for one-to-many, forgetting that calculated columns use row context (not filter context), and assuming bidirectional filtering is always safe (it can cause ambiguity).
A relationship in Power BI connects two tables based on a matching column, allowing you to filter one table using fields from the other without duplicating data.
The most common relationship cardinality is one-to-many (1:*), where one value in the first table can match many values in the second.
Filter direction in a relationship controls how filters propagate; always use 'single' direction from the lookup table to the data table unless you have a specific need for bidirectional filtering.
A calculated column is computed row by row during data refresh and stores its result in the table, making it ideal for row-level labels or static derived fields.
Use the RELATED function in a calculated column to pull a value from a related table into the current table, but only when a valid relationship exists between the two tables.
Never create a relationship on columns that contain nulls or duplicates on both sides, as that leads to ambiguity and incorrect aggregations.
Always distinguish between a calculated column (static row value) and a measure (dynamic aggregation that responds to filters) to choose the right tool for the job.
An active relationship is the default filtering path; an inactive relationship (dashed line) can be used temporarily by activating it with the USERELATIONSHIP function in a measure.
These come up on the exam all the time. Here's how to tell them apart.
Calculated Column
Evaluated once during data refresh and stored in the table as a new column.
Uses row context: operates on each row individually (e.g., quantity * unit price for that row).
Increases the size of the data model because it adds a permanent column to the table.
Measure
Evaluated dynamically each time the report is interacted with (filtered, sliced).
Uses filter context: aggregates values across all rows that match the current filters.
Does not increase the data model size; it is computed in memory when needed.
Active Relationship
Shown as a solid line between two tables in the Model view.
Used by default for all filter propagation between the two tables.
Only one relationship between two tables can be active at a time.
Inactive Relationship
Shown as a dashed line between two tables.
Ignored by default; you must activate it using the USERELATIONSHIP function in DAX.
You can have multiple inactive relationships between the same two tables, e.g., OrderDate and ShipDate both linking to a Dates dimension.
One-to-Many Relationship (1:*)
One value in the lookup table can match many values in the data table.
The most common cardinality in data modelling; simple and performant.
Requires unique values on the 'one' side (no duplicates in the key column).
Many-to-Many Relationship (*:*)
Values on both sides of the relationship can repeat.
Less common and can produce ambiguous results in aggregations.
Often requires a bridge table to resolve into two one-to-many relationships.
RELATED Function
Works only if a relationship already exists between the two tables.
Simpler syntax: just specify the column to return.
Faster performance because it uses the pre-defined relationship path.
LOOKUPVALUE Function
Works even without a relationship between the tables.
Requires you to specify both the column to search and the search value.
Slower because it performs a table scan at query time; use only when a relationship cannot be created.
Mistake
Calculated columns recalculate every time I interact with a report filter or slicer.
Correct
Calculated columns are computed once when the data is loaded into the model and then stored as static values. They do not recalculate on every filter change. Measures recalculate dynamically, but calculated columns do not.
This confusion stems from Excel, where formulas in cells recalculate automatically whenever any input changes. Power BI's in-memory column store behaves differently.
Mistake
I should create a relationship between every pair of tables that share even a vaguely similar column name.
Correct
Relationships should only be created between tables that share a logical business key (e.g., CustomerID, ProductID). Creating unnecessary relationships (e.g., linking 'City' in one table to 'City' in another if they are not intended to filter) leads to ambiguous filtering and incorrect results.
Beginners over-apply relationships out of a fear that data will not connect otherwise. They do not realise that unused relationships can cause filter propagation errors, especially in models with multiple fact tables.
Mistake
A many-to-many relationship is the easiest way to solve complex linking issues.
Correct
Many-to-many relationships are a last resort. They can produce ambiguous results and slow down performance. The recommended approach is to create a 'bridge table' that simplifies the link to two one-to-many relationships.
Many-to-many sounds intuitive (many students, many classes), but Power BI handles them poorly compared to simpler cardinalities. Beginners see the easy 'many-to-many' option in the UI and choose it without understanding the consequences.
Mistake
If I delete a relationship, the calculated columns that used the RELATED function will still work because they have already been computed.
Correct
Calculated columns that use the RELATED function are dependent on the active relationship. If you delete that relationship, the calculated column will break and show errors. The column does not store a snapshot of the related value; it stores the DAX expression, which is re-evaluated on data refresh.
This misconception arises because calculated columns are stored as values, but the values are only valid as long as the underlying relationships are intact. Beginners think 'static' means 'independent'.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A calculated column is stored as a static value in your table and computed once when data refreshes. A measure is a dynamic formula that is recalculated on the fly as you interact with filters and slicers in your report. Use calculated columns for row-level labels or flags, and use measures for aggregations like sums or averages that need to change with user interaction.
No, a relationship requires that both tables share a column with matching data values and compatible data types. If you lack a common column, you can create one using a calculated column (e.g., extracting the year from a date) or by merging tables in Power Query to create a new table with the necessary key.
A many-to-many relationship appears when both columns involved contain duplicate values. To fix it, identify or create a 'bridge table' that contains only the unique values of the shared key. Create two one-to-many relationships: one from the bridge table to each original table. This simplifies filtering and avoids performance issues.
The RELATED function retrieves a value from a column in a related table (on the 'one' side of a one-to-many relationship) and returns it for each row in the current table. Use it inside a calculated column when you want to bring in, for example, the product name from a Products table into a Sales table, assuming a relationship exists on ProductID.
In 90% of cases, choose 'Single' direction from the lookup (dimension) table to the data (fact) table. For example, if you want to filter sales by store, but not filter stores by sales, single direction is correct. Use 'Both' directions only if you need to slice both tables based on each other, but be aware that bidirectional filtering can cause ambiguous results in complex models.
An inactive relationship is a secondary link between two tables that is not used by default for filtering. It is shown as a dashed line. To use it, you must write a measure that includes the USERELATIONSHIP function to temporarily activate that path for a specific calculation. For example, if you have both OrderDate and ShipDate linked to a Dates table, you would have one active and one inactive, then use USERELATIONSHIP in a measure to calculate shipping times.
You've finished Relationships and Calculated Columns. Continue through the PL-300 study guide to build a complete picture of the exam.
Done with this chapter?