Microsoft Power BI Quiz: Related Relatedtable
9 questions · exam conditions
0:00
Related RelatedtableQuestion 1 of 9

A model contains a Products table on the one side of an active one-to-many relationship with a Sales table. The relationship filters from Products to Sales. You must add the product category to every row of Sales as a calculated column.

Which DAX expression should you use?

Sales[Category] = SELECTEDVALUE(Products[Category])
Sales[Category] = RELATED(Products[Category])
Sales[Category] = MAX(Products[Category])
Sales[Category] = CALCULATE(SELECTEDVALUE(Products[Category]))
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Related Relatedtable

Practice Related Relatedtable in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Related Relatedtable, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A model contains a Products table on the one side of an active one-to-many relationship with a Sales table. The relationship filters from Products to Sales. You must add the product category to every row of Sales as a calculated column.

Which DAX expression should you use?

  1. Sales[Category] = SELECTEDVALUE(Products[Category])
  2. Sales[Category] = RELATED(Products[Category]) (correct answer)
  3. Sales[Category] = MAX(Products[Category])
  4. Sales[Category] = CALCULATE(SELECTEDVALUE(Products[Category]))
Explanation: When working with calculated columns in Power BI, the key question to ask is: "Am I retrieving a value from a related table for each individual row?" That's exactly the scenario here — you have a one-to-many relationship from Products to Sales, and you need to pull each product's category into the Sales table row by row. The function built precisely for this purpose is RELATED(). Because the relationship filters from Products (one side) to Sales (many side), each Sales row has exactly one matching Products row. RELATED(Products[Category]) walks that relationship and returns the corresponding category value for each row — making B the correct choice. Here's why the other options fall short. A uses SELECTEDVALUE(), which is designed for measure contexts where a filter or slicer may isolate a single value. In a calculated column, there's no selection context being applied per row, so it won't reliably traverse the relationship. C uses MAX(Products[Category]), which aggregates across the entire Products table and ignores the row-level relationship entirely — it would return the same maximum text value for every row rather than the correct related category. D wraps SELECTEDVALUE() inside CALCULATE(), which does modify filter context, but SELECTEDVALUE() still isn't the right tool for navigating a row-level relationship; RELATED() is. A good rule of thumb for the exam: whenever a calculated column needs a value from the "one" side of a relationship, RELATED() is almost always your answer. Reserve SELECTEDVALUE() for measures that respond to user selections.

Question 2

Products filters Sales through a single-direction one-to-many relationship. A developer claims that RELATED(Products[Brand]) cannot be used in a Sales calculated column unless the relationship is changed to bidirectional.

Which statement correctly evaluates the claim?

  1. The claim is correct because RELATED can navigate only in the configured cross-filter direction.
  2. The claim is incorrect because RELATED can navigate from each many-side Sales row to its one-side Product row. (correct answer)
  3. The claim is correct because calculated columns cannot navigate relationships that use single-direction filtering.
  4. The claim is incorrect because RELATED converts the relationship to bidirectional filtering during model refresh.
Explanation: When working with DAX relationships, it's critical to distinguish between cross-filter direction (which controls how filter context flows between tables) and relationship navigation (which controls how lookup functions like RELATED traverse the model). These two concepts are often confused, and this question tests exactly that distinction. RELATED always navigates from the many-side to the one-side of a relationship — regardless of the cross-filter direction setting. In a single-direction relationship where Products filters Sales, each Sales row already has a foreign key pointing to exactly one Product row. RELATED(Products[Brand]) simply follows that pointer to retrieve the corresponding brand value. No bidirectional filtering is needed for this lookup to work. The developer's claim is therefore incorrect, making B the right answer. A contains the core misconception: cross-filter direction governs how filters propagate between tables (e.g., slicers, WHERE-like behavior), not how RELATED navigates. These are separate mechanisms. C extends the same misunderstanding by applying it to calculated columns specifically — calculated columns can absolutely use RELATED to navigate from many to one in a single-direction relationship. D is entirely fabricated; RELATED does not alter relationship settings during model refresh. Relationships are static model configurations, and DAX functions cannot change them at runtime. A helpful rule of thumb: use RELATED when you're on the many side looking up a value from the one side — direction doesn't matter. Save RELATEDTABLE for the reverse. On exam questions, watch for claims that confuse filter propagation direction with relationship traversal capability.

Question 3

A model directly relates Accounts and Territories by using a many-to-many relationship. A developer creates an Accounts calculated column with TerritoryGroup = RELATED(Territories[Group]). Power BI reports that the expression cannot retrieve the requested value across the relationship.

What is the best explanation?

  1. RELATED requires a regular many-to-one navigation path to a single related row and cannot retrieve a scalar across this limited many-to-many relationship. (correct answer)
  2. RELATED works only with numeric columns, so the text-valued territory group must first be encoded as an integer.
  3. RELATED can cross a many-to-many relationship only when its cross-filter direction is changed from single to both.
  4. RELATED requires the Accounts table to be on the one side, even if each account is associated with several territories.
Explanation: Whenever you see a question about RELATED in Power BI, focus on how it navigates relationships. RELATED follows the data lineage from a child table up to a parent table — specifically, it requires a clear many-to-one path where the current row can resolve to exactly one related row. That single-row guarantee is what allows RELATED to return a scalar value. In a many-to-many relationship, a single Account row can correspond to multiple Territory rows. Because there is no single related row to return, RELATED cannot produce a scalar — Power BI has no way to know which Territory's Group value to retrieve. This is precisely why the error occurs, making A the correct explanation. B is simply false. RELATED works with any data type — text, numeric, date, or boolean. There is no encoding requirement. C misrepresents how cross-filter direction interacts with RELATED. Changing cross-filter direction to Both affects how filters propagate between tables for measures and visuals, but it does not change the fundamental cardinality of the relationship. RELATED still cannot resolve a single row on the many side regardless of filter direction. D is backwards and confused. In a many-to-one relationship, RELATED is used from the many side (e.g., Accounts) to look up the one side (e.g., Territories). The problem here isn't which side Accounts is on — it's that no true many-to-one path exists at all. Study tip: Memorize this rule — RELATED = many-to-one navigation only. If the relationship is many-to-many, reach for LOOKUPVALUE or restructure your model instead.

Question 4

A semantic model uses DirectQuery storage mode. Sales is related many-to-one to Customers. A developer attempts to create the Sales calculated column Region = RELATED(Customers[Region]), but Power BI rejects the definition. The same relationship would support this expression in an Import model.

Which action is most appropriate?

  1. Enable bidirectional filtering because DirectQuery permits RELATED columns only across relationships that filter both ways.
  2. Replace RELATED with RELATEDTABLE because table-valued navigation is supported in DirectQuery calculated columns.
  3. Activate automatic date/time because DirectQuery relationship navigation depends on generated local date tables.
  4. Add the region through a source-side query or change the relevant data to Import mode before creating the calculated column. (correct answer)
Explanation: When working with DirectQuery models in Power BI, a critical limitation to internalize is that calculated columns cannot execute DAX expressions that require row-by-row data retrieval across relationships. In Import mode, data lives in memory, so RELATED() can freely traverse relationships at refresh time. In DirectQuery mode, data stays in the source database, and Power BI cannot issue the per-row relational lookups that RELATED() demands during column definition — so the engine rejects it entirely. The most appropriate solution, answer D, is to either push the logic into the source query itself (a SQL view or a native query that joins the tables and exposes the Region column directly) or switch the relevant table to Import mode, where calculated columns function normally. Both approaches work around the fundamental constraint rather than fighting it. Answer A is a red herring. Bidirectional filtering affects how filter context propagates between tables for measures and visuals — it has nothing to do with enabling RELATED() in DirectQuery calculated columns. That restriction is architectural, not a filter-direction issue. Answer B misunderstands the functions: RELATEDTABLE() navigates from the one-side to the many-side, returning a table of related rows — it's the opposite direction of what's needed here and still wouldn't be supported in a DirectQuery calculated column for the same reasons. Answer C is entirely unrelated. Automatic date/time generates hidden date hierarchies and has no bearing on relationship navigation or the RELATED() function. Study tip: On DirectQuery questions, always ask yourself whether the operation requires in-memory row access. Calculated columns and certain DAX functions that need row-level evaluation are the most common victims of DirectQuery restrictions — memorize this pattern.

Question 5

Sales has two relationships to Date: an active relationship from Sales[OrderDate] to Date[Date] and an inactive relationship from Sales[ShipDate] to Date[Date]. A calculated column in Sales is defined as YearValue = RELATED(Date[Year]).

Which year will the calculated column return, and why?

  1. It returns the ship year because RELATED selects the relationship whose foreign-key column most closely matches the requested value.
  2. It returns blank because RELATED cannot choose when active and inactive relationships connect the same two tables.
  3. It returns the order year because RELATED navigates the active relationship and cannot select the inactive relationship in its syntax. (correct answer)
  4. It returns the later year because RELATED evaluates both relationships and resolves multiple matches by taking the maximum.
Explanation: When working with RELATED in DAX, the key concept to anchor on is relationship traversal rules: DAX always follows the active relationship between two tables unless you explicitly override it using USERELATIONSHIP. In this scenario, Sales connects to Date through two relationships — an active one via OrderDate and an inactive one via ShipDate. When you write RELATED(Date[Year]) inside a calculated column on Sales, DAX looks for a path from Sales to Date and automatically walks the active relationship, which links Sales[OrderDate] to Date[Date]. This means the column returns the order year — confirming that C is correct. Here's why the distractors mislead you. A invents a rule that doesn't exist: RELATED has no logic to evaluate which foreign-key column "most closely matches" anything — it simply follows the active relationship, full stop. B suggests that having two relationships between the same tables causes RELATED to return blank, but that's not true; DAX handles this gracefully by defaulting to the active path without ambiguity. D fabricates another nonexistent behavior — RELATED does not evaluate both relationships simultaneously or resolve conflicts by taking a maximum value. These distractors are designed to make you doubt DAX's deterministic behavior. Your study tip: whenever you see RELATED in a Power BI question, ask yourself which relationship is active between those two tables? — that's always what RELATED uses. To leverage an inactive relationship, you must wrap your measure in CALCULATE with USERELATIONSHIP, and note that this only works in measures, not calculated columns.

Question 6

Customers is on the one side of an active relationship with Sales. Sales contains a Status column. You must create a calculated column in Customers that counts only the related Sales rows whose status is Open.

Which DAX expression should you use?

  1. OpenSales = COUNTROWS(FILTER(RELATEDTABLE(Sales), Sales[Status] = "Open")) (correct answer)
  2. OpenSales = COUNTROWS(FILTER(Sales, Sales[Status] = "Open"))
  3. OpenSales = COUNTROWS(RELATEDTABLE(FILTER(Sales, Sales[Status] = "Open")))
  4. OpenSales = COUNTROWS(RELATEDTABLE(Sales))
Explanation: When writing calculated columns in a many-side table's related context, you need two things working together: a function that traverses the relationship to retrieve related rows, and a filter to narrow those rows down. RELATEDTABLE(Sales) is the key function here — when evaluated inside a Customers calculated column, it automatically returns only the Sales rows related to the current Customers row. Think of it as the "one-to-many navigator." Once you have that related table, you can wrap it in FILTER() to apply conditions, then COUNTROWS() to count the result. That's exactly what A does: COUNTROWS(FILTER(RELATEDTABLE(Sales), Sales[Status] = "Open")) — retrieve the related sales for each customer, keep only the "Open" ones, count them. This is correct. B is the most tempting trap. FILTER(Sales, ...) without RELATEDTABLE scans the entire Sales table for every customer row, ignoring the relationship entirely. Every customer would get the same count — the total number of open sales across all customers. C attempts to nest FILTER inside RELATEDTABLE, but RELATEDTABLE only accepts a table name as its argument — not a table expression. This is a syntax error and will not evaluate. D uses RELATEDTABLE(Sales) correctly to scope to related rows, but never applies any filter, so it counts all related sales regardless of status — not just the open ones. Study tip: In calculated columns on the "one" side, always use RELATEDTABLE() to scope to related rows before filtering. The pattern COUNTROWS(FILTER(RELATEDTABLE(Table), condition)) is extremely common on Power BI exams — memorize it.

Question 7

Employees is on the many side of a relationship with Departments. The calculated column DepartmentName = RELATED(Departments[DepartmentName]) works correctly. A developer copies the same expression into a measure, but the measure produces an error. The measure should return a department name only when exactly one employee is in context.

Which change correctly addresses the problem while continuing to use relationship navigation?

  1. Make the Employees-to-Departments relationship bidirectional so that the measure can propagate filter context back to Employees and retrieve the department name.
  2. Wrap RELATED in CALCULATE so that the visual's filter context is converted into an Employee row context that RELATED can navigate.
  3. Iterate VALUES(Employees[EmployeeKey]) and call RELATED inside that iterator, which creates the Employee row context required by the function. (correct answer)
  4. Replace RELATED with RELATEDTABLE(Departments) so that the measure uses a table-valued function, which is compatible with measure filter context.
Explanation: When you see a question about RELATED failing in a measure, the core issue is row context vs. filter context. RELATED navigates a relationship from the current row to a related table — but "current row" only exists inside row context, which measures don't have by default. Measures operate in filter context, so calling RELATED directly in a measure leaves it with no row to navigate from, causing an error. The fix in C is correct because iterating VALUES(Employees[EmployeeKey]) creates a row-by-row scan over Employees, and inside that iterator, each iteration establishes a row context on the Employees table. RELATED can then follow the many-to-one relationship up to Departments and retrieve the department name. Using VALUES with a single key also naturally enforces the "exactly one employee in context" requirement — if more than one employee is visible, you'd need additional handling (like HASONEVALUE), but the mechanism itself is correct. A is wrong because bidirectional relationships affect how filter context propagates between tables, not whether RELATED can execute. Changing the cross-filter direction doesn't create a row context, so RELATED would still fail. B is wrong because CALCULATE converts row context into filter context — the opposite of what you need. It doesn't manufacture a new row context for RELATED to use. D is wrong because RELATEDTABLE navigates in the opposite direction (one-to-many, returning a table of related rows), and it still requires row context. It doesn't make relationship navigation "compatible" with filter context. Your study tip: whenever RELATED appears in a measure scenario, immediately ask yourself — "where does the row context come from?" If there's no iterator (SUMX, MAXX, FILTER, etc.), RELATED will fail.

Question 8

A Sales table contains Quantity, and a related Products table contains UnitPrice. Products is on the one side of an active one-to-many relationship with Sales. You need a measure that sums quantity multiplied by the related unit price for every visible Sales row.

Which measure should you create?

  1. Revenue = SUM(Sales[Quantity]) * RELATED(Products[UnitPrice])
  2. Revenue = SUMX(Products, Products[UnitPrice] * RELATED(Sales[Quantity]))
  3. Revenue = SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice])) (correct answer)
  4. Revenue = SUMX(Sales, Sales[Quantity]) * MAX(Products[UnitPrice])
Explanation: Whenever you see a question about multiplying values across related tables in DAX, your first instinct should be to reach for SUMX — an iterator that evaluates an expression row by row before aggregating. SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice])) is correct because it walks through each row in the Sales table, retrieves the related unit price from Products using RELATED, multiplies the two values together, and then sums all those row-level results. This is precisely the pattern for computing revenue: Revenue=∑each saleQuantityi×UnitPricei\text{Revenue} = \sum_{\text{each sale}} \text{Quantity}_i \times \text{UnitPrice}_i A fails because SUM(Sales[Quantity]) collapses the entire Quantity column into a single scalar before any multiplication occurs. At that point, RELATED has no row context to operate in, so the formula will throw an error — RELATED only works inside a row-by-row iteration. B iterates over the Products table instead of Sales. Even if this ran without error, RELATED would try to follow a many-to-one path from Products back to Sales, which is the wrong direction and would not return meaningful per-sale quantities. D uses SUMX(Sales, Sales[Quantity]), which simply sums quantity with no multiplication inside the iterator, then multiplies by a single MAX price across the entire Products table — giving one flat price rather than each product's correct price. Study tip: On Power BI DAX questions, if you need to multiply two columns from related tables row by row, the pattern is always SUMX(<fact table>, <fact column> * RELATED(<dimension column>)). Memorize this template.

Question 9

Products is related one-to-many to Sales. You need a calculated column in Products that returns the latest transaction date for each product. Some products have no Sales rows and should return blank.

Which DAX expression meets the requirement?

  1. LatestSale = RELATED(Sales[TransactionDate])
  2. LatestSale = MAX(Sales[TransactionDate])
  3. LatestSale = MAXX(Sales, Sales[TransactionDate])
  4. LatestSale = MAXX(RELATEDTABLE(Sales), Sales[TransactionDate]) (correct answer)
Explanation: When writing calculated columns in Power BI, you need to think carefully about row context vs. filter context and how relationships flow. A calculated column evaluates row-by-row on the table it lives in — here, that's the "one" side (Products). To reach data on the "many" side (Sales), you must explicitly navigate the relationship. D is correct because RELATEDTABLE(Sales) fetches all Sales rows related to the current Products row, giving MAXX a proper table to iterate over. MAXX then walks each related Sales row and returns the maximum TransactionDate. When a product has no sales, RELATEDTABLE returns an empty table and MAXX naturally returns blank — exactly what the requirement asks for. A fails immediately because RELATED navigates from the "many" side to the "one" side — it's designed for lookup columns, not for pulling aggregated data down from a child table. Used on the Products table, it would throw an error or return incorrect results. B looks tempting but is a classic trap. Writing MAX(Sales[TransactionDate]) in a calculated column on Products does not automatically filter to only that product's sales. Without explicit relationship navigation, it evaluates the entire Sales column and returns the same global maximum for every product row. C is very close but subtly wrong. MAXX(Sales, Sales[TransactionDate]) also iterates the entire Sales table rather than just the related rows for each product, producing the same global-maximum problem as B. Study tip: In a calculated column on the "one" side of a relationship, always use RELATEDTABLE() to scope your aggregation to matching rows on the "many" side.