Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is optimizing a Power BI model for a large dataset. The model has several relationships between tables. The modeler observes that a relationship between 'Orders'[CustomerID] and 'Customers'[CustomerID] is inactive, but is occasionally needed for specific calculations that diverge from the default active relationship. How should the modeler activate this inactive relationship for a specific measure without affecting other parts of the model?
- AUse the TREATAS DAX function to mimic the relationship.
- BCreate a duplicate table and an active relationship to it.
- CChange the relationship to active in the model view.
- DUse the USERELATIONSHIP DAX function within a CALCULATE statement.
Show answer & explanationAnswer & explanation
Correct answer: D. Use the USERELATIONSHIP DAX function within a CALCULATE statement.
USERELATIONSHIP temporarily activates an inactive relationship for the duration of a CALCULATE statement, allowing specific measures to use it without changing the model's default behavior.
Why the other options are wrong
- A. TREATAS applies a table expression as filters to an unrelated table, it doesn't activate an existing inactive relationship.
- B. Creating duplicate tables increases model complexity and size, and is not the most efficient way to handle inactive relationships.
- C. Changing it to active affects the entire model, which is not desired for specific calculations.
USERELATIONSHIP Function
USERELATIONSHIP is a DAX function that enables an inactive relationship between two columns to be used for the duration of a specific calculation.
- Temporarily overrides the default (active) relationship behavior.
- Must be used as an argument within the CALCULATE function.
- Useful when multiple relationships exist between tables but only one can be active by default.
- Improves model flexibility without duplicating tables or changing active relationships globally.
Memory trick: Manage relationships like a switchboard: turn on the right connection for the call.