Relationship direction and cross-filtering in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Single vs Both Direction Relationships

In Power BI, what is the main difference between a single direction and a both direction relationship in a data model?

A Single direction filters data only from one table to another, while both direction filters data in both tables simultaneously.

B Single direction allows filtering only on numeric columns, both direction allows filtering on text columns.

C Single direction relationships are faster but less accurate, both direction are slower but more accurate.

D Single direction relationships only work with one-to-one cardinality, both direction only with many-to-many.

💡 Hint

Think about how filters flow between tables when you slice data.

Single direction relationships filter data from one table to another in one direction only. Both direction relationships allow filters to flow both ways, affecting both tables.

❓ dax_lod_result

intermediate

Effect of Relationship Direction on Measure Calculation

Given two tables: Sales and Products, connected by a single direction relationship from Products to Sales, what will be the result of this measure when filtered by a Product Category?

Sales Amount = SUM(Sales[Amount])

A The measure will sum sales for all categories except the selected one.

B The measure will correctly sum sales only for the selected Product Category.

C The measure will return blank because the filter cannot flow from Products to Sales.

D The measure will sum all sales ignoring the Product Category filter.

💡 Hint

Consider the direction of the relationship and where the filter is applied.

The single direction relationship from Products to Sales allows filters on Products to affect Sales, so filtering by Product Category correctly filters the Sales Amount.

🔧 Formula Fix

advanced

Troubleshooting Unexpected Filter Behavior

You have two tables: Customers and Orders. The relationship is single direction from Customers to Orders. You create a measure to count orders filtered by Customer Region, but the result does not change when you select different regions. What is the most likely cause?

A The relationship direction is incorrect; it should be from Orders to Customers.

B The relationship is inactive and needs to be activated.

C The measure uses an aggregation that ignores filters.

D The filter is applied on Orders table, but the relationship only filters Orders from Customers.

💡 Hint

Think about how filters flow and where the filter is applied.

Since the relationship filters from Customers to Orders, applying a filter on Orders does not affect Customers. If the filter is applied on Orders, it won't affect the measure counting orders by Customer Region.

❓ visualization

advanced

Choosing the Best Visual to Show Cross-Filtering Effects

You want to demonstrate how changing relationship direction affects filtering between Products and Sales tables. Which visualization setup best shows the impact of cross-filtering direction?

A A slicer on Product Category filtering a sales amount card visual.

B A table visual showing all sales records without any filters.

C A pie chart showing sales by region with no interaction with product slicers.

D A matrix visual showing products and sales with no slicers or filters.

💡 Hint

Think about how slicers and visuals interact with relationships.

A slicer on Product Category filtering a sales amount card visual clearly shows how filters flow from Products to Sales, demonstrating the effect of relationship direction.

🎯 Scenario

expert

Designing a Data Model for Bidirectional Filtering

You have three tables: Customers, Orders, and Products. You want to enable filtering from Customers to Orders, Orders to Products, and also from Products back to Orders and Customers. Which relationship directions should you set to achieve this without creating ambiguity or circular dependencies?

A Set Customers to Orders as single direction, Orders to Products as both direction, and Products to Orders as single direction.

B Set all relationships as both direction to allow full filtering in all directions.

C Set Customers to Orders and Orders to Products as both direction, avoid setting Products to Orders to prevent circular filtering.

D Set Customers to Orders and Orders to Products as single direction, then create a calculated table to enable reverse filtering.

💡 Hint

Think about how both direction relationships can cause circular filter paths.

Setting Customers to Orders and Orders to Products as both direction allows filtering down and up the chain without circular dependencies. Avoid setting Products to Orders as both direction to prevent ambiguity and circular filtering errors.