cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Trouble with relationships on simple data model

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):

data-model.png

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Continued Contributor
Continued Contributor

@Nemza 

 

Creating bridging table automatically:

 

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

mhossain_0-1599625392033.png

 

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.

 

 

 

 

 

View solution in original post

5 REPLIES 5
Continued Contributor
Continued Contributor

@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.

Thanks very much @mhossain !

 

I think option 1) will work well for me. 

How do I go about creating a bridging table automatically?

 

Community Support
Community Support

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Continued Contributor
Continued Contributor

@Nemza 

 

Creating bridging table automatically:

 

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

mhossain_0-1599625392033.png

 

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.

 

 

 

 

 

View solution in original post

Thank you! Very helpful

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors