Relationship model: PostgreSQL and composite keys

[heidibrak]

Hello everyone,

I've got a PostgreSQL connected to PowerBI and my db looks like this - reduced to the relevant parts:

Table 1: Its id, let's call it id1 is the Primary Key.

Table 2: id1 is the Foreign Key referencing id1 from Table 2. Its Primary Key is a composite key consisting of id1 AND id2

Table 3: id1+id2 is the Foreign Key and id1+id2+id3 is the Composite Primary Key of Table 3.

When I connect to the db via PowerBI and look at the relationships there's a one:many relationship between Table 1 and Table 2, just as I want. There's no relationship between Table 2 and Table 3 though and the little info I've found on this issue seems to be quite old. Can anyone confirm that this does NOT work automatically and needs to fixed manually after connecting to the database?

Unfortunately I need my PowerBI users to be able to use the db without fixing something beforehand. 

[v-zhouwen-msft · Accepted solution]

Hi @heidibrak ,

Regarding your question, in Power BI, you need a column to establish a relationship between tables (compound keys containing multiple columns are not supported), you need to merge all the columns contained in the compound key into a single column. You can do this in Power Query.

You can then publish the modified .pbix file to Power BI Service to share it with your users, and they won't have to modify it manually.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.