Return column from inactive relationship

[ronnie_roberts]

I see plenty of ways to use an inactive table relationship to return a measure but I haven't seen anything about using an inactive table relationship to return a column.  Our Jira system provides a user name as a foreign key in about a dozen different tables but obviously we only have 1 user table.  Is there some formula in DAX that will let me temporarily use an inactive relationship (without using LOOKUP or generating a new table)?

Example:

Users

UserID

Username

FullName

Features

FeatureID

FeatureName

FK_FeatureOwner_ID (active relationship to UserID above)

Sprints

SprintID

SprintName

FK_SprintOwner_ID (inactive relationship to UserID above)

[OwenAuger · Accepted solution]

Thanks 🙂

If you are wanting to add a column to a table on the many-side of an inactive 1:many relationship with the User table, you can certainly use a pattern like that on the page linked above.

Using the tables you posted, if you want to add a column to the Sprints table corresponding to Users[FullName] corresponding to FK_SprintOwner_ID, you could try:

Sprint Owner Name (calc column) =
CALCULATE (
    MAXX ( Sprints, RELATED ( Users[FullName] ) ),
    USERELATIONSHIP ( Sprints[FK_SprintOwner_ID], Users[UserID] ),
    ALL ( Users )
)

Adjust as necessary depending on the table/column names involved.

Does something like this work?

Regards

[OwenAuger]

@ronnie_roberts 

Are you wanting to retrieve values from Users corresponding to rows of Sprints, using the inactive relationship?

Possibly you're looking for something similar to the expression discussed in this article:

https://www.sqlbi.com/daxpuzzle/userelationship/solution

Note that the above is the solution to a puzzle, which is here:

https://www.sqlbi.com/daxpuzzle/userelationship/

Could you clarify exactly what result you need and are you talking about a calculated column or measure that retrieves the value based on the inactive relationship?

Regards

[ronnie_roberts]

I would love to be able to produce something similar to below where Feature Owner and Story Owner are both coming from the Users table:

Feature ID	Feature Name	Feature Owner	Story ID	Story Name	Story Owner
1	Feature1	George Washington	1	Story1	John Adams
1	Feature1	George Washington	2	Story2	Thomas Jefferson
1	Feature1	George Washington	3	Story3	Abe Lincoln
2	Feature2	John Kennedy	4	Story4	Richard Nixon