Inactive relationships in Data Model

[rpmitchell]

Now I have a question regarding relationships between tables in the 2013 Data Model. It seems that when I create a relationship between 2 lookup tables, power pivot will automatically make it "Inactive". Why is that? I haven't read anything about Inactive relationships, or that they can exist. In the Diagram view, it shows as a dotted line, and in the Manage relationships dialog box, it shows the status as inactive. Why are some relationships inactivated?

 

[scottsen]

Typically that happens because you can't have 2 relationships between the same 2 tables... even indirectly through another table. I think it usually gives you a warning/error about "there is already a relationship between these 2 tables" ?

 

[rpmitchell]

No warning. I was trying to relate the Charges table to the Current month ATB TABLE. Both of theses are already lookup tables to the Adjustments table. It let me build it, but when I tried pivoting, I got a "relationship may be needed" message. When I checked the manage relationship dialogue box, I saw the inactive relationship. The common key between all tables is the account ID, which is what I use to build all the relationships. I guess it's because there is an indirect relationship already established, like you said. My workaround was building a calculated column in the Charges table using the Lookupvalue function, which works without a relationship. I found that trick in the PowerPivot Alchemy book.