Filters from many-to-many table working inconsistently

[Brightsider]

So, I have the following data model setup coming from Dynamics:

Essentially, Opportunity contains our opportunities for Sales to push our product. Each Opportunity is tied to a customer Account, which belongs to a single sales Territory. So far, so good. Where things get tricky is the Territory Members table: One Territory will have many salespeople assigned to it but also one salesperson can belong to different territories. So, if I want to filter the Opportunities table using the Territory Name and Territory User fields, both coming from the Territory Members table, I get some odd results. The top level Territory Name filters the data correctly:

However, when I expand down to the Territory Member Name, it behaves as though it can't filter the names:

My hunch is that since I'm relating to the Territory Members table by way of territory, it doesn't have a means of narrowing down which member actually owns the Opportunity, but I don't know how I would test that hunch or what I would do to resolve it if that's the case.

If needed, here is my Sample Data

[Brightsider]

Done. 🙂

[Anonymous]

Hi,@Brightsider 

Regarding the issue you raised, my solution is as follows:

Sorry for being late, but has your problem been solved? If it does, share your solution and accept it as a solution, which will help members of the community who share your problem find a solution faster.

Here are the solutions we offer:

1.According to your requirements, we infer that the reason for this is that the relationship was not established correctly when you established the table relationship. You can try to re-establish the table relationship. Here is the relevant documentation:
Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

2.Of course you can also use the ISINSCOPE() function, here are the relevant use cases:

Measure = 
IF(ISINSCOPE(financials[Product]),"second",IF(ISINSCOPE('financials'[Country]),"fisrt"))

ISINSCOPE function (DAX) - DAX | Microsoft Learn

3.Here are some relevant documents to help you:

Create a matrix visual in Power BI - Power BI | Microsoft Learn 

According to the data you provided, we cannot provide you with a complete solution for the time being, especially for the part of table relationship. We hope that you can provide a complete pbix file with sensitive information removed, which will help us analyze the reason for you better.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Brightsider · Accepted solution]

Thank you for the additional help. As far as I can tell, because the reason for this issue is because there is no direct relationship between Opportunity and Territory Member, it is only accessed through Territory. So, since each Opportunity will join to every Territory Member without some additional filter to narrow down which Territory Member a given Opportunity belongs to, it cannot filter at the Territory Member level.

The solution was to reorganize the data model: I grabbed the territorymemberid that the Opportunity belongs to using SQL and stored that within the Opportunity table, then used that to establish a relationship directly from Opportunity to Territory Member.

[Anonymous]

Hi,@Brightsider 

Could you please authorize us to access your example data, which we are unable to open for the time being, so that we can perform further accurate analysis of your problem against the data.
Below is a screenshot of our application:

Best Regards,

Leroy Lu