Many-To-Many

[toddpbi]

Hello PBI community.

I have been stuck on this particular problem for a few days and cannot seem to wrap my head around it.
Currently, I have sales and target data in a table that is related to a specific payer for each transaction. I want to be able to drill down through my current hierarchy from KAM (Key Account Manager) to the specific sales agent relating to the the KAM. This relationship is defined as many-to-many (1 sales agent can have many key account managers depending on the core business field), and one key account manager is responsible for many agents.

I have created a bridge table here that contains a hierarchy for each payer. This table contains no unique columns so I cannot directly create a relationship between the hierarchy and the sales data. When I slice by sales agent or unit leader, the sales data appears to slice correctly. However, when slicing by KAM, the data does not filter sales agents or unit leader information correctly (simply repeats the same value across all agents and leaders).

KAM     Sales Agent
1           10
1           11

3           11

4           12

5           13

6           12

6           14

This would be an example of the tables.
KAM 1 is responsible for many sales agents (10, 11), while sales agent number 11 reports to many KAMs (1, 3).
I also have a "unit leader" dimension that falls in between the sales agent and KAM. A single unit leader can have many KAMs and also many sales agents.

Could anyone help me out here and identify where my issue lies? I have attached an image of my current relationships.
Thanks for your help!

[SivaMani]

@toddpbi,

Can you create two Dimension tables,

1. DimKAM - Which has unique KAM IDs
2. DimSA - Which has unique Sales Agent IDs

And Create the relationship between three tables,

1. DimKAM to your Sales/Target table - it should be bi-directional.
2. DimSA to your Sales/Target table - it should be bi-directional.

[toddpbi]

I cannot create a relationship between these tables as there is no unique values on which to create the relationship.

[SivaMani]

Oh Sorry,

I forgot to mention, You have created those two tables and each has a single column.

• DimKAM for KAM
• DimSA for Sales Agent

[toddpbi]

I already have the correct tables with unique values down the bottom (KAM Information, Sales Agent Information etc).

The only table that does not contain unique values is the Hierarchy table.

[SivaMani]

Use those tables (KAM Information, Sales Agent Information etc) and create relationships with Sales Information and Targets