Forum Discussion

Valnus's avatar
Valnus
Helper II
4 years ago
Solved

Table Relationships

I have two tables 

 

1) Dataset

2) Date Table 

There is no relationship between the two that Power Bi can pickup. I can link Assessment Dates column to the corresponding Date table month for a link. 

But when I create a index using power query in both and link them up that way my results change in my visuals. 

Why and what is the correct way ? 

I have a feeling there is a concept I am not 100% understanding 





Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi Valnus 

     

    If the sole purpose of creating index columns in both tables so that you can create a relationship betwen them (link the tables) then you don't need to do that.  PBI should, and by the looks of it has, identified the relationship and created it.

     

    You can now create visuals/reports/measures based off the 2 tables by the relationship that exists between them.

     

    Regards

     

    Phil

5 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi Valnus 

     

    If your dataset contains dates and your date table contains all dates from Jan 1 to Dec 31 in the years listed in your dataset then things should work fine.  PBI should create a relationship between the two tables - there's no need for a separate index.

     

    Without seeing your file it's hard to say what is going wrong - please supply it.

     

    Regards

     

    Phil

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi Valnus 

     

    Thanks for the file.  I can see that there's a 1-M relationship between the Date Table and the dataset so that is correct.

     

    What is it you are trying to do that is not working?  Why do you need to create an Index?  What is its purpose?

     

    Regards

     

    Phil

    • Valnus's avatar
      Valnus
      Helper II
      4 years ago

      Thanks 

       

      I wanted to create two index colums in each table to create the link as I did not see the date link between the two tables and I created it by dumb luck.

      Maybe my understanding of this in Bi needs a correction. 

      But is the idea of a duel index columns a bad one ? 

       



      • PhilipTreacy's avatar
        PhilipTreacy
        Super User
        4 years ago

        Hi Valnus 

         

        If the sole purpose of creating index columns in both tables so that you can create a relationship betwen them (link the tables) then you don't need to do that.  PBI should, and by the looks of it has, identified the relationship and created it.

         

        You can now create visuals/reports/measures based off the 2 tables by the relationship that exists between them.

         

        Regards

         

        Phil