Trying to Use Slicers to Narrow down a Table with little Success

[tinyninja]

So I have a table of deals that I want to filter. Here are some important facts:

1. One deal can be assigned to several users of the same role

2. Each user can be assigned to more than one role

3. Each user can be assigned to the same application more than once under different roles

4. The main table of deals can only contain one row for each deal

My goal is to have one filter that narrows down the deal list by whether anyone of a given role has been assigned, another filter that specifically narrows the list by whether a certain person is assigned, and the ability to combine the filters to find all deals with a specific role assigned and a specific user in that role.

I have tried to build relationships to capture this interaction, but I just can't get it right. I know there is something I am missing and need some help to figure this out. 

Main Table: 

Application ID	Managers	Underwriters	BDMs	Deal Amount
1	John Doe, Jane Kim	Helen Bane	Helen Bane, Luke Hanson	$500,000
2	John Doe	Luke Hanson	Luke Hanson	$555,000
3	John Doe	Luke Hanson	Helen Bane	$333,000
4	Jane Kim	Luke Hanson	Helen Bane, Luke Hanson	$250,000
5	Jane Kim	Luke Hanson	Helen Bane	$777,000
6	Jane Kim	Luke Hanson	Helen Bane	$620,000
7	Jane Kim	Luke Hanson	Luke Hanson	$150,000
8	John Doe		Luke Hanson	$888,000
9				$345,000

Users Table: 

Name	User ID
John Doe	111
Jane Kim	122
Helen Bane	133
Luke Hanson	144

Roles Table:

Role	User ID
Manager	111
Manager	122
Underwriter	133
Underwriter	144
BDM	133
BDM	144

Assigned Managers:

Application ID	User ID
1	111
1	122
2	111
3	111
4	122
5	122
6	122
7	122
8	111

Assigned Underwriters:

Application ID	User ID
1	133
2	144
3	144
4	144
5	144
6	144
7	144

Assigned BDMs:

Application ID	User ID
1	133
1	144
2	144
3	133
4	133
4	144
5	133
6	133
7	144
8	144

Relationships I have tried:

Hopefully you guys can help me with this. Thanks so much

[v-junyant-msft]

Hi @tinyninja ,

Can you elaborate on what problem you are having with "but I just can't get it right"? Ideally you would like to have a screenshot to illustrate the error you are experiencing and what you are hoping to get out of it.
But based on the information you've provided so far, I think that may be the problem:

Many-to-many relationships have a lot of limitations and can also allow many operations to yield incorrect results. You can try to optimize the many-to-many relationship to see if you can get the desired result.
Many-to-many relationship guidance - Power BI | Microsoft Learn

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tinyninja]

Was thinking maybe I need different tables for the users assigned to each role but it doesn't work for creating a slicer and also creates ambiguity from what I recall.