- Which year will the calculated column return, and why?
Answer: It returns the order year because RELATED navigates the active relationship and cannot select the inactive relationship in its syntax.
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.
- Which change correctly addresses the problem while continuing to use relationship navigation?
Answer: Iterate VALUES(Employees[EmployeeKey]) and call RELATED inside that iterator, which creates the Employee row context required by the function.
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.
- Which DAX expression meets the requirement?
Answer: LatestSale = MAXX(RELATEDTABLE(Sales), Sales[TransactionDate])
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.
- Which DAX expression should you use?
Answer: OpenSales = COUNTROWS(FILTER(RELATEDTABLE(Sales), Sales[Status] = "Open"))
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.
- Which statement correctly evaluates the claim?
Answer: The claim is incorrect because RELATED can navigate from each many-side Sales row to its one-side Product row.
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.
- Which measure should you create?
Answer: Revenue = SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))
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
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. - Which action is most appropriate?
Answer: Add the region through a source-side query or change the relevant data to Import mode before creating the calculated column.
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.
- What is the best explanation?
Answer: 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.
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.
- Which DAX expression should you use?
Answer: Sales[Category] = RELATED(Products[Category])
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.
