Power BI Relationships: Cardinality and Cross-Filter Direction Quiz Quiz

Explore foundational concepts of relationships in data modeling, focusing on cardinality types and cross-filter direction settings. This quiz helps users understand how to configure, identify, and troubleshoot relationships for accurate data analysis in Power BI.

1. Identifying One-to-Many Relationships

   Which cardinality type should be used if a Customer table has unique Customer IDs, but many Orders exist for each customer?

   1. Many-to-One (*:1)
   2. One-to-One (1:1)
   3. One-to-Many (1:*)
   4. Many-to-Many (*:*)

   Explanation: The correct cardinality is One-to-Many (1:*), as each customer appears only once but may have multiple related orders. Many-to-One (*:1) is the reverse and does not fit this case. Many-to-Many (*:*) is used when both tables have non-unique values. One-to-One (1:1) requires both sides to have only unique values, which isn't true here.

2. Defining Many-to-Many Cardinality

   If both the Product and Sales tables contain multiple duplicate Product IDs, which relationship cardinality is suitable?

   1. Sum-to-Sum ($:$)
   2. One-to-Many (1:*)
   3. Many-to-Many (*:*)
   4. One-to-One (1:1)

   Explanation: Many-to-Many (*:*) is appropriate when neither table contains only unique values for the related columns. One-to-One (1:1) requires uniqueness, One-to-Many (1:*) needs one unique side, and Sum-to-Sum ($:$) is not a valid option. The correct choice supports complex schema scenarios.

3. Cross-Filter Direction Definition

   What does setting the cross-filter direction to Both enable in relationships between two tables?

   1. Increasing cardinality values
   2. Filtering in both directions
   3. Enabling duplicate rows
   4. Blocking all filters

   Explanation: Setting the cross-filter direction to Both means filters can propagate from either table to the other, allowing more flexible data exploration. It does not introduce duplicate rows nor block filters. Increasing cardinality is unrelated; direction only controls filter flow, not data volume.

4. Best Use Scenario for Single Cross-Filter Direction

   When is it recommended to use a Single cross-filter direction rather than Both?

   1. When tables are always empty
   2. When relationships are inactive
   3. When avoiding ambiguous filter paths
   4. When columns have missing values

   Explanation: Using Single cross-filter direction helps prevent ambiguity in models with multiple relationship paths between tables. Tables being empty or relationships inactive are unrelated to filter direction. Missing values in columns do not dictate the direction setting; ambiguity in filter flow is the primary concern.

5. Unique Identifier Selection

   Which column characteristic is most important when setting it as the 'one' side in a One-to-Many relationship?

   1. Can have many duplicates
   2. Is always a text data type
   3. Contains only unique values
   4. Includes special characters

   Explanation: A column must have only unique values to serve as the 'one' side in a One-to-Many relationship, ensuring each value matches to many on the other side. The data type (such as text), presence of duplicates, or inclusion of special characters does not affect this requirement, as uniqueness is the key property needed.

6. Impact of Incorrect Cardinality

   What is a possible consequence of defining a relationship with incorrect cardinality between two tables?

   1. Faster report loading
   2. Inaccurate data aggregations
   3. Disabling schema updates
   4. Reduced storage size

   Explanation: Incorrect cardinality can result in faulty data aggregations because filters and joins won't operate correctly. It does not make reports load faster, nor does it disable schema updates or reduce storage size. Ensuring correct cardinality is essential for valid analytical results.

7. Identifying the 'Both' Direction Issue

   In a complex data model, what problem might arise from using Both cross-filter direction on multiple relationships?

   1. Elimination of all duplicates
   2. Automatic fixing of data types
   3. Circular dependencies between tables
   4. Creation of calculated columns

   Explanation: Using Both cross-filter direction across multiple tables can create circular dependencies, causing errors in the model. Automatic data type fixing and calculated column generation are unrelated to filter direction. Using Both does not eliminate duplicates; rather, it affects how filters flow across tables.

8. Inactive Relationships Meaning

   What does it mean when a relationship is marked as 'inactive' in a data model?

   1. It always enforces referential integrity
   2. It permanently deletes data
   3. It combines columns automatically
   4. It does not filter data unless specifically activated

   Explanation: An inactive relationship does not influence filtering or calculations unless activated by special functions. It does not delete data or automatically combine columns. Referential integrity enforcement is not guaranteed just because a relationship exists or is inactive.

9. Effect of Missing Relationships

   If no relationship exists between two relevant tables, what can happen during analysis?

   1. Measures from one table may not produce expected results when combined with fields from the other
   2. Columns are sorted alphabetically
   3. All data is merged automatically
   4. Relationships are automatically inferred

   Explanation: When there is no relationship, measures might behave unexpectedly because filter context isn't passed between tables. The data is not automatically merged, columns aren't simply sorted, and not all systems infer relationships automatically. Correct table linkage is required for accurate outcome.

10. Identifying the 'One-to-One' Scenario

    In which situation should you use a One-to-One (1:1) relationship between tables?

    1. When you want to hide columns
    2. If data is updated frequently
    3. When tables have the same number of columns
    4. Both tables have columns with only unique values and each row corresponds to exactly one row in the other table

    Explanation: A One-to-One relationship is appropriate when both tables have uniquely matching columns and each row corresponds directly. Hiding columns, having the same number of columns, or frequent data updates are unrelated to the correct use of One-to-One relationships. The key is unique matching on both sides.