Forum Discussion

ronnie_roberts's avatar
ronnie_roberts
Icon for Helper I rankHelper I
3 years ago
Solved

Return column from inactive relationship

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)
Need Help
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      ronnie_roberts
      Icon for Helper I rankHelper I
      3 years ago

      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 IDFeature NameFeature OwnerStory IDStory NameStory Owner
      1Feature1George Washington1Story1John Adams
      1Feature1George Washington2Story2Thomas Jefferson
      1Feature1George Washington3Story3Abe Lincoln
      2Feature2John Kennedy4Story4Richard Nixon
  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    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