Table Relationships

[Valnus]

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 

[PhilipTreacy]

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]

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 · Accepted solution]

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

[PhilipTreacy]

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

[Valnus]

https://drive.google.com/drive/folders/1sNw6b8iXFLeJbD0lWaZkwedaldVSOgYE?usp=sharing