Forum Discussion
Table Relationship Doesn't Work
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.
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.
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.
8 Replies
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.
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.
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?
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
Hi,
So please show the exact result which you are expecting.
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.
