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

N to N Relationships between 3 tables

Hi, 

There's already a couple posts on this but didn't find one with my exact issue. 

I have 3 tables, one for my projects : 

projects.JPG

 

One for the Invoice table 

timelog.JPG

One for the association table between invoice<->projects : 

assoc.JPG

 

 

Goal is to get for each client the total time spent on a project with the total amount invoiced like this : 

result.JPG

I've been trying to add relationships using PowerBI but it only accepts 1 to N relationships. And as i have : 

123 / C001
123 / C002 

 

It doesn't allow me to create it. 

 

 

I've read in some posts that it could be possible to write a query as a workaround ? 
Is that correct ? And do you have any idea of the query that could be used ? 

 

Thanks!  

 

 

 

Error i'm getting (french) : 

error.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: N to N Relationships between 3 tables

Hi @deanmit,

 

Here we can enable Composite models to create many to many relationship directly, the many to many  feature is available in both July and August release, you can download the new Power BI desktop from here. For more details, please refer to the online document. Also attached the pbix for your reference.

 

https://www.dropbox.com/s/wkz09eckysvx6qk/N%20to%20N%20Relationships%20between%203%20tables2.pbix?dl...

 

 

1.png2.png

 

Regards,

Frank

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

View solution in original post

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: N to N Relationships between 3 tables

Hi @deanmit,

 

Here we can enable Composite models to create many to many relationship directly, the many to many  feature is available in both July and August release, you can download the new Power BI desktop from here. For more details, please refer to the online document. Also attached the pbix for your reference.

 

https://www.dropbox.com/s/wkz09eckysvx6qk/N%20to%20N%20Relationships%20between%203%20tables2.pbix?dl...

 

 

1.png2.png

 

Regards,

Frank

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

View solution in original post

deanmit Frequent Visitor
Frequent Visitor

Re: N to N Relationships between 3 tables

Hello @v-frfei-msft

 

Thanks for your reply. I installed latest version, enabled many to many relations in the options and gave it a try ! 

I just don't get how you have those results. Here's what i get : 

result-n.JPGqsqsd

Where did you actually put your pbix to see how you did that??? 
I don't seem to be able to find it :'(

 

Regards, 

Dean

deanmit Frequent Visitor
Frequent Visitor

Re: N to N Relationships between 3 tables

@v-frfei-msft, working Smiley Happy

 

The difference is the 1-1 relationship between "association" and "invoice" table. I had a 1-N. 

 

Thanks a lot for the pbix ! 

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 218 members 2,381 guests
Please welcome our newest community members: