Relating tables with repeated fields

[Macaulay]

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

[iamprajot · Accepted solution]

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

[Macaulay]

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!

[iamprajot]

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.

[Macaulay]

It worked meanwhile with my data. The problem was, that there were some uncertain values by which the dates could not be connected. Thanks for the help!

[Macaulay]

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.