Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is optimizing a Power BI model for a financial institution. The model contains a 'Transactions' fact table and a 'Accounts' dimension table. The 'Transactions' table has a 'DebitAccountID' and a 'CreditAccountID', both related to 'Accounts[AccountID]'. The modeler wants to create measures that can dynamically filter transactions based on whether an account was involved as a debit or a credit. Which DAX function is essential for enabling multiple active relationships between 'Transactions' and 'Accounts' in measures?
- ARELATEDTABLE
- BUSERELATIONSHIP
- CCROSSFILTER
- DTREATAS
Show answer & explanationAnswer & explanation
Correct answer: B. USERELATIONSHIP
USERELATIONSHIP is a DAX function that enables a specific inactive relationship for the duration of a CALCULATE function's evaluation. This is crucial when you have multiple relationships between two tables and need to dynamically choose which one to activate for a particular measure.
Why the other options are wrong
- A. RELATEDTABLE is used to retrieve a table of related rows from the 'many' side of a relationship, not to activate relationships for filtering.
- C. CROSSFILTER modifies the cross-filter direction of a relationship but doesn't activate an inactive relationship for a specific calculation.
- D. TREATAS applies the result of a table expression as filters to columns from an unrelated table, used for disconnected tables, not for activating inactive relationships between related tables.
USERELATIONSHIP Function
A DAX function that enables an inactive relationship between two tables for the duration of a CALCULATE function's evaluation, allowing dynamic selection of relationships.
- Used when multiple relationships exist between two tables.
- Activates an inactive relationship within a CALCULATE context.
- Essential for 'role-playing dimensions'.
Memory trick: Use relationship (USERELATIONSHIP) to pick the right path.