Issue with using tables with inactive relationship

Hi k_h_s 

Need to combine data from 3 tables where one relationship is inactive, but USERELATIONSHIP isnot giving the expected row count. Problem is that USERELATIONSHIP only activates the relationship for filter propagation, not for creating the join needed for table visual.

When we add fields from Table 3 and Table 2 to a table visual, Power BI needs to figure out how to combine the rows. With the active relationship between Table 1 - Table 3, filters flow correctly. However, the inactive Table 1 - Table 2 relationship doesnot create the needed "join" to combine Table 2 and Table 3 data row-by-row. USERELATIONSHIP inside a measure only affects filter context for that measure's calculation.

Create an Active Bridge Table
This creates the proper many-to-many relationship structure your data needs.
Create a bridge table that contains all valid combinations of your linking column:
Bridge_Table =
DISTINCT(
UNION(
SELECTCOLUMNS(Employee_Master, "LinkKey", Employee_Master[MergedcolumnPREC]),
SELECTCOLUMNS(Earning_codes, "LinkKey", Earning_codes[MergedcolumnPREC])
)
)

Set up relationships
Connect Bridge_Table[LinkKey] to Employee_Master[MergedcolumnPREC] (1 to *)

Connect Bridge_Table[LinkKey] to Earning_codes[MergedcolumnPREC] (1 to *)

Keep Employee_Master → Company_Master active

Try with the visual by adding fields from Company_Master and Earning_codes.

USERELATIONSHIP() can only be used inside a measure, so it can’t “change the join” for raw columns the way SQL does. That’s why your “as-is fields from Table 2 + Table 3” table visual won’t behave like your SQL SELECT DISTINCT ... JOIN ... when the key relationship is inactive.

If your goal is literally the SQL result (distinct combinations of EarnCode, PRCo, Name, HQCo), you have two practical options:

Option 1 (best practice): model fix (don’t rely on USERELATIONSHIP for this)
Create the correct active path by introducing a proper bridge/dimension key so the relationships can be active without ambiguity. In your case, if the real grain is (PRCo, EarnCode), make a small bridge table with unique pairs and relate both Employee and Earnings to it (or split your merged key back into two columns and relate properly). Then the table visual can show columns “as-is” with no measure tricks.

Option 2: create a calculated table that reproduces the SQL join
This gives you exactly the “distinct rows” output you want, and then you put that table in the visual:

EmployeeEarnCompany =
DISTINCT (
    SELECTCOLUMNS (
        NATURALINNERJOIN (
            NATURALINNERJOIN ( Employee_Master, Company_Master ),
            Earning_codes
        ),
        "EarnCode", Earning_codes[EarnCode],
        "PRCo",     Employee_Master[PRCo],
        "Name",     Employee_Master[Name],
        "HQCo",     Company_Master[HQCo]
    )
)

(If your join columns don’t share the same names, don’t use NATURALINNERJOIN—use TREATAS/CROSSJOIN+FILTER, but the idea is the same: build the SQL-shaped result table once.)

Why your row count changes with USERELATIONSHIP: because you’re still showing columns, and columns follow the existing filter/relationship paths; USERELATIONSHIP doesn’t apply unless a measure is being evaluated, and even then it only affects the filter propagation for that measure.

If you truly don’t want any aggregation/measure, don’t use USERELATIONSHIP for this—either fix the model with a proper bridge, or materialize the SQL result as a calculated table.