Forum Discussion

bml123's avatar
bml123
Icon for Post Patron rankPost Patron
4 years ago
Solved

Power BI relationships between tables

Hi,

 

I have 2 tables

1) Clients - 

Client IdNameAddressRundate
1  01/10/2021
2  01/10/2021
3  01/10/2021
1  15/10/2021
3  15/10/2021

 

2) ClientDetails

Client IdSalesProductRundate
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?

Need Help

4 Replies

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      bml123
      Icon for Post Patron rankPost Patron
      4 years ago

      Hi vojtechsima 

       

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

      • vojtechsima's avatar
        vojtechsima
        Icon for Super User rankSuper User
        4 years ago

        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.

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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