Bird
Raised Fist0
Power BIbi_tool~5 mins

Active vs inactive relationships in Power BI - Quick Revision & Key Differences

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Recall & Review
beginner
What is an active relationship in Power BI?
An active relationship is the main connection between two tables that Power BI uses by default for filtering and calculations.
Click to reveal answer
beginner
What is an inactive relationship in Power BI?
An inactive relationship exists between tables but is not used by default. It can be activated temporarily in calculations using DAX functions.
Click to reveal answer
intermediate
How do you use an inactive relationship in a DAX formula?
You use the USERELATIONSHIP() function in DAX to activate an inactive relationship temporarily within a calculation.
Click to reveal answer
intermediate
Why would you use inactive relationships in your data model?
Inactive relationships let you model multiple ways tables relate without confusion, like different date fields (order date vs ship date) in the same tables.
Click to reveal answer
beginner
Can you have more than one active relationship between two tables in Power BI?
No, Power BI allows only one active relationship between two tables. Others must be inactive.
Click to reveal answer
What does an active relationship do in Power BI?
AIt is ignored unless activated in DAX.
BIt deletes duplicate data.
CIt is used by default for filtering and calculations.
DIt creates a new table.
How can you use an inactive relationship in a measure?
ABy using the USERELATIONSHIP() function in DAX.
BBy deleting the active relationship.
CBy refreshing the data model.
DBy creating a new table.
How many active relationships can exist between two tables in Power BI?
AZero
BTwo
CUnlimited
DOne
Why use inactive relationships in your data model?
ATo delete unused columns.
BTo model multiple ways tables relate without confusion.
CTo speed up data refresh.
DTo create duplicate tables.
Which DAX function activates an inactive relationship?
AUSERELATIONSHIP()
BCALCULATE()
CFILTER()
DRELATED()
Explain the difference between active and inactive relationships in Power BI and when you would use each.
Think about how Power BI chooses which relationship to use automatically.
You got /4 concepts.
    Describe how to use an inactive relationship in a DAX measure and why this is useful.
    Focus on the DAX function that switches relationships.
    You got /4 concepts.

      Practice

      (1/5)
      1. What is the main difference between an active and an inactive relationship in Power BI?
      easy
      A. Active relationships are used automatically in calculations, inactive require USERELATIONSHIP()
      B. Inactive relationships are used automatically, active require USERELATIONSHIP()
      C. Both active and inactive relationships work automatically without extra functions
      D. Inactive relationships cannot be used in any calculations

      Solution

      1. Step 1: Understand active relationships

        Active relationships are automatically applied in calculations and visuals without extra effort.

      2. Step 2: Understand inactive relationships

        Inactive relationships do not apply automatically; you must use USERELATIONSHIP() in DAX to activate them temporarily.

      3. Final Answer:

        Active relationships are used automatically in calculations, inactive require USERELATIONSHIP() -> Option A

      4. Quick Check:

        Active = automatic, inactive = USERELATIONSHIP() [OK]
      Hint: Active works by default; inactive needs USERELATIONSHIP() [OK]
      Common Mistakes:
      • Thinking inactive relationships work automatically
      • Confusing USERELATIONSHIP() usage
      • Believing inactive relationships cannot be used
      2. Which of the following is the correct syntax to activate an inactive relationship in a measure?
      easy
      A. CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date]))
      B. CALCULATE(SUM(Sales[Amount]), ACTIVERELATIONSHIP(Sales[Date], Calendar[Date]))
      C. SUM(Sales[Amount]) + USERELATIONSHIP(Sales[Date], Calendar[Date])
      D. CALCULATE(SUM(Sales[Amount]), RELATIONSHIP(Sales[Date], Calendar[Date]))

      Solution

      1. Step 1: Recall USERELATIONSHIP() usage

        USERELATIONSHIP() is used inside CALCULATE() to activate an inactive relationship temporarily.

      2. Step 2: Check syntax correctness

        CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date])) uses CALCULATE with USERELATIONSHIP and correct column references, matching the correct syntax.

      3. Final Answer:

        CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date])) -> Option A

      4. Quick Check:

        USERELATIONSHIP inside CALCULATE is correct syntax [OK]
      Hint: USERELATIONSHIP() must be inside CALCULATE() [OK]
      Common Mistakes:
      • Using ACTIVERELATIONSHIP() which does not exist
      • Adding USERELATIONSHIP() outside CALCULATE()
      • Using RELATIONSHIP() instead of USERELATIONSHIP()
      3. Given the following measure:
      Sales Amount by Order Date = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[OrderDate], Calendar[Date]))

      What will this measure calculate if the active relationship is between Sales[ShipDate] and Calendar[Date]?
      medium
      A. Sum of Sales Amount filtered by ShipDate
      B. Sum of Sales Amount ignoring any date filter
      C. Sum of Sales Amount filtered by OrderDate using inactive relationship
      D. Error because USERELATIONSHIP cannot be used with OrderDate

      Solution

      1. Step 1: Identify active and inactive relationships

        The active relationship is Sales[ShipDate] to Calendar[Date]. The relationship Sales[OrderDate] to Calendar[Date] is inactive.

      2. Step 2: Understand USERELATIONSHIP effect

        Using USERELATIONSHIP activates the inactive relationship on OrderDate temporarily for this measure.

      3. Final Answer:

        Sum of Sales Amount filtered by OrderDate using inactive relationship -> Option C

      4. Quick Check:

        USERELATIONSHIP activates inactive relationship [OK]
      Hint: USERELATIONSHIP switches to inactive relationship filter [OK]
      Common Mistakes:
      • Assuming measure uses active ShipDate filter
      • Thinking USERELATIONSHIP causes error
      • Ignoring inactive relationship usage
      4. You wrote this measure:
      Total Sales = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date]))

      But it returns the same result as SUM(Sales[Amount]). What is the most likely problem?
      medium
      A. CALCULATE cannot be used with USERELATIONSHIP
      B. The relationship between Sales[Date] and Calendar[Date] is already active
      C. The Sales[Date] column does not exist in the table
      D. USERELATIONSHIP() is misspelled and causes a syntax error

      Solution

      1. Step 1: Check relationship status

        If the relationship between Sales[Date] and Calendar[Date] is active, USERELATIONSHIP() has no effect because the active relationship is already used.

      2. Step 2: Understand measure behavior

        The measure behaves like SUM(Sales[Amount]) filtered by the active relationship, so results match.

      3. Final Answer:

        The relationship between Sales[Date] and Calendar[Date] is already active -> Option B

      4. Quick Check:

        USERELATIONSHIP only affects inactive relationships [OK]
      Hint: USERELATIONSHIP only changes inactive relationships [OK]
      Common Mistakes:
      • Assuming USERELATIONSHIP causes syntax error
      • Thinking CALCULATE cannot use USERELATIONSHIP
      • Ignoring relationship status
      5. You have two date columns in Sales: OrderDate and ShipDate. The active relationship is between ShipDate and Calendar[Date]. You want a report showing total sales by OrderDate without changing the active relationship. Which approach is best?
      hard
      A. Create a new Calendar table for OrderDate and link it actively
      B. Change the active relationship to OrderDate and remove ShipDate relationship
      C. Use the active relationship and ignore OrderDate column
      D. Create a measure using CALCULATE with USERELATIONSHIP(Sales[OrderDate], Calendar[Date])

      Solution

      1. Step 1: Understand relationship setup

        ShipDate is active; OrderDate relationship is inactive to Calendar[Date]. Changing active relationship affects all reports.

      2. Step 2: Use USERELATIONSHIP in measure

        Using CALCULATE with USERELATIONSHIP activates the inactive OrderDate relationship temporarily without changing model.

      3. Step 3: Evaluate other options

        Changing active relationship or creating new Calendar table is more complex and less flexible.

      4. Final Answer:

        Create a measure using CALCULATE with USERELATIONSHIP(Sales[OrderDate], Calendar[Date]) -> Option D

      5. Quick Check:

        USERELATIONSHIP enables alternate date filtering [OK]
      Hint: Use USERELATIONSHIP to activate alternate date without model changes [OK]
      Common Mistakes:
      • Switching active relationship instead of using USERELATIONSHIP
      • Ignoring inactive relationship usage
      • Creating unnecessary new tables
      Recommended

      Ready to prove it?

      Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

      10 questions~5 mineasy hard