Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 25
CASE ANALYSIS:
You are a data analyst at HealthConnect Analytics, a company that provides data-driven insights to insurance providers. Your team has been tasked with analyzing healthcare claims data from multiple sources to identify trends in costs, processing times, and utilization. The project involves creating a dashboard that provides stakeholders with actionable insights into claim trends. The final deliverable should include metrics such as:
Total number of claims
Average claim amount
Distribution of claims by state
Trends in claim processing time
However, the raw data is inconsistent, unclean, and requires significant transformation before it can be used for reporting.
Raw Data Overview
Key Challenges
Business Problem
The current state of the data makes it unsuitable for analysis due to its unclean and inconsistent format. You must resolve the following problems to achieve the project goals:
Question:
How should you create a relationship between the Claims and StateCode tables in Power BI?
-
A
Merge the StateCode table into the Claims table.
-
B
Use StateName as the key and set the relationship to Many-to-One.
-
C
Create a custom key using ClaimID and StateName.
-
D
Use StateName as the key and set the relationship to Many-to-Many.
Reveal correct answer
Correct answer: B
Explanation
Solution with Table Relationship
At HealthConnect Analytics, you need to link the Claims table (ClaimID, PatientName, StateName, etc.) to the StateCode table (StateName, StateCode) to support metrics like distribution of claims by state. The best approach is to create a Many-to-One relationship using StateName as the key. In Power BI Desktop, go to the "Model" view. Ensure both tables are loaded: Claims with multiple rows per StateName (e.g., "California" appearing often) and StateCode with unique StateName entries (e.g., "California" once, linked to "CA"). Drag StateName from StateCode (one side) to Claims[StateName] (many side) to create the relationship. In the "Manage Relationships" dialog, confirm the cardinality as Many-to-One (Claims to StateCode) and set the cross-filter direction to "Single" (StateCode filters Claims). This links claims to their state codes for dashboard visuals (e.g., maps using StateCode).
Why This Works
StateName is the common field between Claims and StateCode, with Claims having multiple instances (many) and StateCode having one per state (one), fitting a Many-to-One relationship. This enables filtering and aggregation (e.g., claims by StateCode) while maintaining a star schema—Claims as the fact table, StateCode as a dimension. The Microsoft documentation on relationships explains, “A Many-to-One relationship connects a fact table with repeated values to a dimension table with unique values, using a shared key like StateName, optimizing analytics.” This supports the project’s state distribution metric efficiently.
Why Not Other Options?
Many-to-Many with StateName: Many-to-Many is unnecessary and less performant here—StateCode has unique StateName entries, not requiring this complexity, per the many-to-many documentation.
Merge StateCode into Claims: Merging bloats the Claims table with redundant StateCode data (e.g., "CA" repeated), violating star schema principles and increasing model size, per merge documentation.
Custom key with ClaimID and StateName: ClaimID is unique to Claims and absent in StateCode, making this irrelevant—StateName alone suffices as the key.
A Many-to-One relationship via StateName ensures HealthConnect’s tables are properly linked for accurate state-based analysis.
A.
Merging the tables combines them into one, effectively flattening the relationship. While this simplifies the model, it eliminates the benefits of maintaining a separate dimension table (e.g., flexibility, reusability). It may also lead to data redundancy.
Why it's incorrect:
Merging is unnecessary in this case, as Power BI's relationship feature can efficiently connect the tables. Flattening the model reduces scalability and modularity.
B.
This option creates a relationship between the Claims table and the StateCode table using StateName as the common key. Setting it to Many-to-One is appropriate if each state in the StateCode table is unique, and multiple claims can reference the same state.
Why it's correct:
This is the correct choice because StateName serves as a shared key between the two tables, and the logical relationship (many claims to one state) is properly modeled. This ensures accurate filtering and data integrity during analysis.
C.
Creating a composite key using ClaimID and StateName is inappropriate, as ClaimID is specific to individual claims, not states. This approach would create an incorrect relationship structure and fail to capture the Many-to-One relationship between claims and states.
Why it's incorrect:ClaimID is unrelated to the StateCode table, and using it as part of a key introduces an invalid relationship, leading to incorrect filtering and aggregation.
D.
A Many-to-Many relationship allows both tables to have duplicates in the key column. While this might work in some cases, it is not ideal here because the StateCode table should have unique StateName entries, and Many-to-Many relationships can introduce unnecessary complexity and potential errors.
Why it's incorrect:
This approach unnecessarily complicates the data model and could result in ambiguous relationships, leading to inaccurate filtering and aggregation.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
