6

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.

Charles
  • 50,943
  • 13
  • 104
  • 142
Mike
  • 1,349
  • 1
  • 17
  • 23

1 Answers1

5

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]
)
Bill Anton
  • 2,920
  • 17
  • 23
  • This seems like exactly what I want to do...So I tried: =CALCULATE(VALUES('People'[FullName]),USERELATIONSHIP('Def'[OwnerID],'People'[PersonID])) However, I get the error: Calculation error in column 'Def'[]: A table of multiple values was supplied where a single value was expected. I must not be doing something right :( – Mike Jan 18 '13 at 01:51
  • can you post a screenshot of the PowerPivot data model showing the relationship between the tables ? – Bill Anton Jan 18 '13 at 13:30
  • Sure! I uploaded it here: [link](http://flic.kr/p/dMxzVz). And to be clear the function i have in there is: =CALCULATE(VALUES(People[FullName]),USERELATIONSHIP(FollowUps[OwnerID],People[PersonID])) – Mike Jan 18 '13 at 16:03
  • Is the relationship defined between FollowUps and People tables using FollowUps.OwnerId and People.PersonId ? – Bill Anton Jan 18 '13 at 16:28
  • I'm not sure I follow what you're asking. The relationships are defined by a ForeignKey - PrimaryKey Relationship. The model was generated by me connecting to SQLServer and it pulled in everything automatically. – Mike Jan 18 '13 at 16:41
  • in the diagram view, if you double click on the line connecting the two tables (FollowUps and People)...can you post a screenshot of that? – Bill Anton Jan 18 '13 at 16:47
  • ahhh, weird...looks like calculation I proposed only works when there are multiple relationships between the tables and one is active. In your diagram, if there is only 1 relationship between FollowUps/People, then why is it marked inactive? – Bill Anton Jan 18 '13 at 18:35
  • That's a great question. So I tried to mark it as active and I get: You cannot activate the relationship because a set of active relationships already exists between tables ActionRequiredOwners and People. But your solution of LOOKUPVALUE works! Thank you so much! – Mike Jan 18 '13 at 20:02
  • Late to the party, but to answer why your relationship is marked inactive is because you cannot have multiple active direct or indirect relationships between two tables. e.g., A -> C and A -> B -> C will mark either B -> C or A -> C as inactive. – Tanveer Badar Dec 04 '21 at 04:43