cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Many-To-Many

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!

2018-05-21_12h19_50.png

5 REPLIES 5
Highlighted
Solution Sage
Solution Sage

@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.

 

Highlighted

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

Highlighted

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
Highlighted

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.

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors