Trouble with relationships on simple data model

[Nemza]

Hi there, 

I'm struggling with a Power BI concept in the way that relationships are handled. 

I come from a SQL background, so this data model seems pretty straight forward to me, however it breaks PowerBI when I try to use it. 

My data model is as follows (i've highlighted the relationship fields):

All the relationships are active and Power BI doesn't complain at this point. 

When I try to display data from the ProjectGroup - ProjectGroupManager-User tables, everything is fine. 

The same goes for displaying data from ProjectGroup - ProjectGroupProject - Project

As soon as I try to data from Project and User together, I get a "Can't determine relationships between the fields error".

I'm not sure why this is the case when the data is related through the ProjectGroup table?

Please see pbix attached: https://drive.google.com/open?id=18B5k4LJPEvDz80sDq73Zm3MZCUJmWFkN&authuser=nemanja.stabic%40digiata...

Any help is appreciated!

[v-diye-msft]

Hi @Nemza 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[mhossain]

@Nemza 

From the relationship diagram you can see the arrow direction and see it is obvious between User and Project tables, for dax it will be hard to identify the relationship. For the simplicity can you do the below:

1). Create a mapping/new table with all the Unique IDs(this table can be automatic), and then create relationship from this table to all the tables. And it should work.

2). I think end objective will be to create some calculation/measures and drag on the visuals, right? So when you create the measures, create virtual relationship like use "TREATAS"

Please let me know if above makes sense.

[Nemza]

Thanks very much @mhossain !

I think option 1) will work well for me. 

How do I go about creating a bridging table automatically?

[mhossain · Accepted solution]

@Nemza 

Creating bridging table automatically:

1. Go to 'Modeling' tab in your pbi and click 'New Table'

2. write dax, I am assuming, your all tables names like, Table1, table2, Table3 and so on........ and ID field name is 'id'

UniqueAllids =
DISTINCT(

UNION(

SELECTCOLUMNS(Table1,"id",Table1[id]),
SELECTCOLUMNS(Table2,"id",Table2[id]),
SELECTCOLUMNS(Table3,"id",Table3[id])
))

3. Now you have all the unique IDs from all the tables, and try the relationship to all the tables, on the visual, drag fields from this table, so you can bring more fields to this table, above dax is just an example of one field, you can add more fields to this.

Let me know if you face any challenges.

[Nemza]

Thank you! Very helpful