Table Relationship Doesn't Work

[AJ007]

I have two tables. One with employee initials (e.g. AB, JS) and number of tasks they have done. I have another table with same employee initials and dates they worked on. I want to have a single visual which displays employee name, number of tasks and date they worked on.

As both tables have employee initials which are repeated; it doesn't create an automatic relationship.

I used a method which creates another table with unique employe initials. I can now set up relationship either manually or autodetecting.

I still can't get that single visual with data from both original tables. Please help.

[AJ007 · Accepted solution]

Sum or otherwise, it still didn't work and it wouldn't even take me to the level you managed to get to.

I think there is something wrong with this version of Power BI. I was using the latest January 2018 update.

I will redownload and install. Thanks.

[AJ007 · Accepted solution]

Actually what I did is I added a new column called ID in both Excel files. Power BI then automatically established a 1 to 1 relationship in both direction and I was able to get the single visual I wanted.

I don't know the consequences of doing this in a larger and actual dataset but it worked for what I wanted to achieve in this test.

Thanks.

[avisingh]

Ashish (AJ007), I recorded a video for you. See if this helps.

⚡Power On!⚡
Avi Singh
YouTube / Facebook / Twitter / Web

[AJ007]

First of all Kudos to you for making a video on this !! You are amazing !!

There may be some problem with the way I have modelled this data; so you are right when you find weird results in the visuals.

None the less, the ways you have shown in this video will no doubt get me closer to what I want to acheve.

Just a small question, if we didn't have summed the tasked in this video, would it show up correct numbers ? I will try it and let you know.

Thanks heaps.

[AJ007 · Accepted solution]

Sum or otherwise, it still didn't work and it wouldn't even take me to the level you managed to get to.

I think there is something wrong with this version of Power BI. I was using the latest January 2018 update.

I will redownload and install. Thanks.

[Ashish_Mathur]

Hi,

How does one know the date which needs to be assigned to the particular row in Table1 i.e. for row 1 of Table1, what date should be assigned - 1 January or 4 January?  Also, will all names on Table1 also be on Table2 and vice versa?  Will the order of the names be the same on both Tables?

[AJ007]

Your first question is very thought provoking. I just created this dataset as a test. I have another dataset which I actually want to work on and I can't share it here for privacy reasons.

About your second questions, yes all the names on Table1 will also be on Table2 and vice versa.

Going back to your first question, I was expecting it would bring all the possible combinations in one "Table" visual but I have just started with Power BI so I am not too sure.

Thanks Ashish

[Ashish_Mathur]

Hi,

So please show the exact result which you are expecting.

[AJ007]

I was expecting something like this