How to link more than one lookup table using the same column

[Siraj.Samsudeen]

I have a table called Country and I have a number of lookup tables which classify country and they are all joined to the main country table using the countryID. I am trying to create a relationship between these tables, but powerpivot is not allowing me to create a relationship using the same column. Is there a reason why Powerpivot is doing that and is there a workaround?

 

[MD610]

The final answer for your situation really depends on what exactly you are trying to do with the 2 lookup tables. PowerPivot should allow you to relate them but it will be am inactive relationship if they are both already related to another table by the same field. You can specify which relationship you want active with DAX in a measure by using USERELATIONSHIP() as a filter arguement in CALCULATE().

Also, and again this depends on what you are trying to achieve, you can get the lookup tables to behave like they are related if you have a measure from the fact table (in your case Country) in your pivot. For example, if you have a field from lookup1 in your pivot rows, and then add a field from lookup2 below that in the rows, you will see every value of lookup1 with every value of lookup2 below it. But if you add a measure from Country to this pivot (something like a count or other measure of your data from the Country table) then the pivot will "filter" both lookup rows appropriately because values for that measure that don't appear in both lookup tables will be left blank and essentially disappear from your pivot (if the default option in PivotTable Options is still set to do so).

Ultimately, the best option depends on your final goal.

 

[Laurent C]

From the description of your problem, I guess your relationships look like Table1 <- Country, Table2 <- Country, ... I suggest you revert your relationships. The table Country should be your lookup table. Table1, Table2, ..., should depend on the Country table.

 

[Siraj.Samsudeen]

Thanks MD610 and Laurent for the answers.

I think a little more description of the problem will help you to understand my situation better and to suggest a more appropriate solution.

My fact table has sales data organized by customers. Then my customer is linked to country table using countrycode. Now, I have a number of classification tables which give many attributes for countries. For example, I have a region table which has Country Code and Region Name and an Cluster table which contains the cluster that the country belong to. If the database is designed correctly, both Region and Cluster should be lookup tables and country should have lookup columns pointing to them (as Laurent suggested). But here the problem is that I don't have control over the schema. I got 2 reports containing Country Code, Region in one and Country Code and Cluster in another. I already have another DB query for the main country table with other properties. Now, Ideally, I want to turn these also into other attributes for country so that I can group sales by region or cluster as well as by country name.

 

[Laurent C]

Do I get this right? You cannot guarantee that each country code will only appear once your tables with (Country, Region) and (Country, Cluster) ...
If this is the case, you cannot add a new relationship between both tables, because there already exists one (through the Country table). Your problem would then be that filters applied on the Region Name would not automatically propagate to the other table.
One (tedious) solution would be to write your measures following the many-to-many patterns.
Probably better:
You could modify your main DB query to include the information from the other data sources (PowerPivot stores the data anyway, so it should not affect performance - only updates)
Otherwise, you can link both tables to your Country table (only through the Country Code column) and create calculated columns in the Country table using the LOOKUPVALUE function. The LOOKUPVALUE function may return an error, if a Country Code is associated with more than one Region or more than one Cluster. But in that case, you would have to take a second look at your data / model anyway.
Side note: I have a problem that carriage return no longer works on this forum (I must use Notepad instead). Is it only me?

 

[Siraj.Samsudeen]

Do I get this right? You cannot guarantee that each country code will only appear once your tables with (Country, Region) and (Country, Cluster) ...

Hi Laurent, I can guarantee that each country code appears only once in my Region and Cluster tables. There is no many-2-many here. The problem is that the relationship is inverted. I get the Country table loaded directly from DB and I got the other 2 tables in an Excel file from users. Now, I have to add the Region and Cluster attribute to the Country so that it shows up under Country as a property. I can't do a single query to combine them as the data come from different databases.

Right now, I have a relationship between my customer and country based on CountryCode. Now, I have to link Region and Cluster to the Country table using the same CountryCode and CountryCode is unique in all these 3 tables as one country can be assigned to only one Region and only one Cluster. So, I do not understand why PP is stopping me from doing it.

Side note: I have a problem that carriage return no longer works on this forum (I must use Notepad instead). Is it only me?

No, I don't have this problem. What browser do you use? I used Google Chrome on both Windows and Mac and I don't have this issue.

 

[Laurent C]

Well, I used IE10 (on Windows 7) and compatibility mode does not helpl. Thanks for the feedback.
Returning to your issue, PowerPivot only accepts one (active) relationships between two tables. This restriction also applies to implied relationships.
In your case, since there will only be one occurrence of a specific Country Code in these tables, then you can directly link the Country table to your other tables (using them as lookup tables). Even if you could, you probably would not need that.
Once you have built your relationships, then you can just add calculated columns in the Country table using the RELATED function.
If this does not work, I suggest you share a link to your workbook, so we can look into your model.

 

[Siraj.Samsudeen]

Hi Laurent, thanks for the answers. I think the problem might have to do with the version of Powerpivot I have. I had v1 till now and just after encountering this problem and being unable to solve it, I upgraded it to V2 and the problem seems to have gone away.