Using RELATED function in DAX with USERELATIONSHIP

Question

I am trying to pull in a field from another table in my BISM model using the "RELATED" function. Since there are many tables that have relationship to the People table, only ONE relationship is active and the rest are inactive. In this case, the relationship is Inactive, and as I understand it I am to use the "USERELATIONSHIP" function to specify the relationship to use. Based on what I found here: http://connect.microsoft.com/SQLServer/feedback/details/730493/powerpivot-dax-method-related-does-not-work-if-key-is-inactive I thought I could do this:

=CALCULATE(RELATED(People[FullName]]),USERELATIONSHIP(Def[OwnerID],People[PersonID]))

But I get the error:

The column 'People[FullName]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Answer 1

Here's an example using the AdventureWorksDW data set...

EVALUATE(
    CALCULATETABLE(
        ADDCOLUMNS(
            'Internet Sales'
            ,"Order Calendar Year"
                ,CALCULATE(VALUES('Date'[Calendar Year]))
            ,"Ship Calendar Year"
                ,CALCULATE(
                    VALUES('Date'[Calendar Year])
                    ,FILTER(
                         'Date'
                        ,'Date'[DateKey] = 'Internet Sales'[ShipDateKey]
                    )
                )
            ,"Due Calendar Year"
                ,CALCULATE(
                     VALUES('Date'[Calendar Year])
                    ,FILTER(
                         'Date'
                        ,'Date'[DateKey] = 'Internet Sales'[DueDateKey]
                    )
                )
        )
    )
)

This code pulls in Calendar Year from the Date table to the Internet Sales table for each of the dates in the Internet Sales table:

• Order Date (active relationship)
• Due Date (inactive relationships)
• Ship Date (inactive relationships)

EDIT: corrected first response (above)...Here's a good write up of why USERRELATIONSHIP won't work in this sceanrio: linky*

The LOOKUPVALUE below works as well:

=LOOKUPVALUE(
     People[FullName]
    ,People[PersonID]
    ,FollowUps[OwnerID]
)