Forum Discussion
Relating tables with repeated fields
Dear all,
Till now I worked with Qlik Sense and enjoyed the QLIK engine, by which related columns are automatically related. I'm new to BI and what I found out till now is that BI does it another way. I am working with sales manager's names and as these names occure more often in one column I can't connect them the way I'm used to. I now, that I have to use Excel Power Query and did it as explained here:
http://radacad.com/relationship-in-power-bi-with-multiple-columns
My problem: Table1 contains the sales manager, his costumers and their annual revenue, the other table contains the sales managers, his costumers and different revenues from projects. So when merged, my merged table shows me the annual revenue repeatedly, as I have different revenues for projects that run on one and the same customer and sales manager. I would like to upload an example, but I couldn't find where to.
Thanks for the help.
Tom
I don't get it, what the Problem is but here is something that might help with the Relationships.
Go to Modeling Tab and Create new Table based on Distinct Values so that there are no Repeated Values
Table = DISTINCT(TableName[ColumnName])
Then Create Relationship of other Tables with this one,
Go to Home Tab and Manage Relationships to Edit or Create Relationships
Select Table Name and Manager Column Name in both Tables
Also, Cross Filter Direction should be Both
5 Replies
I don't get it, what the Problem is but here is something that might help with the Relationships.
Go to Modeling Tab and Create new Table based on Distinct Values so that there are no Repeated Values
Table = DISTINCT(TableName[ColumnName])
Then Create Relationship of other Tables with this one,
Go to Home Tab and Manage Relationships to Edit or Create Relationships
Select Table Name and Manager Column Name in both Tables
Also, Cross Filter Direction should be Both
Thanks for your reply, I will test it out. For better understanding, this is what my unrelated tables look like before:
So what has to be related is the customer ID.
When I let Excel power query relate it, the result looks like this:
I am not sure whether I could use such data in a senseful way in Power BI, because I'm used to other BI software.
So i tried your solution, creating new tables and creating relationships with this one works perfect, but there's no change in my data when I select after a field, that appears in both tables and is connected as you mentioned.
Maybe see my example above for further ideas, as I have no clue whether the solution is the right one for my problem. Thank you anyway!
Still not sure what u doing but it worked on mine.
Table A and B are joined using Sales Manager and not the Customer.
Once u do that click on Relationship Manager and do Auto Detect.
