Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) |
Solved! Go to 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
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
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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |