Power BI relationships between tables

[bml123]

Hi,

I have 2 tables

1) Clients - 

Client Id	Name	Address	Rundate
1			01/10/2021
2			01/10/2021
3			01/10/2021
1			15/10/2021
3			15/10/2021

2) ClientDetails

Client Id	Sales	Product	Rundate
1			01/10/2021
2			01/10/2021
3			01/10/2021
1			15/10/2021
3			15/10/2021

I have got many to many active relationship on Client id between both the tables.

I also want to have an active relationship on Rundate on both the tables. But can't create another active relationship as there is one already existing on Client Id.

So, created a Date table and created an active relationship between ClientDetails and Date on RunDate.

But unable to create another active relationship between Clients and Date table.

I have a slicer on Date field where I need to show the details from both the tables whatever date I choose.

Basically, I want to create an active relationship on client id and run date between Clients and ClientDetails. How do I achieve that?

[v-luwang-msft · Accepted solution]

Hi  @bml123 ,

Between two table,you cannot have more than one active relationship, you have to use userelationship function to use inactive relationship.

Similar case refer:

https://community.powerbi.com/t5/Desktop/Multiple-active-relationships-between-2-Tables/td-p/928298 

And you could also rfer the following article:

Use Multiple Connections Between Tables

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[vojtechsima]

Hi, @bml123 ,
Slicer from Date Table, Table with all fields from Both Tables, relationship like this (Date and ID):

Is this something you want? Also, having Many to Many relationships is always not the best option, ideally if you had one table that has unique IDs. Like this, it's very bad design.

[bml123]

Hi @vojtechsima 

Yes, i want active relationship on id and date. How do I achieve that?

[vojtechsima]

@bml123 
Well, you can't create a "circle" between tables, so ideally you create Date Table and map it with Clients Details or Details depends how you wanna use it, like this:

And then map Client and Clients ID together by ID, like this:

However, Many to Many is not the best practice, I strongly suggest creating a Clients Details table, where every ID is unique.