Forum Discussion
Power BI
HI All,
I have 3 tables Append1, BridgeTable and Teamsize the relationship we have between three tables are Many-Many relationship, whereas in bridgetable I have only Pid's. I need to fetch the team size from the table called Teamsize and show that in a report which has developed using Append1 table.
I have built the relationship in model view, I have single-directional method for this table access. Append to Bridgetable is Many-Many and Bridgetable to Teamsize Many-One
Here my issue is: In the teamsize table I have projects and their allocated team size count and I have Client level count's as well for example: "XXX" is the client so under the XXX I have multiple projects, when I select client level count it is getting displayed but when I select any individual project from the client I'm still getting the overall Client count not changing dynamically
Hi Shahnaz_Fathima ,
Your issue is happening because of the Many-to-Many (M:M) relationship between Append1 and BridgeTable. In such cases, filtering behavior doesn't always work as expected.
Why is the project selection not affecting the count?
You have a Many-to-Many (M:M) relationship between Append1 and BridgeTable, which can sometimes cause unexpected aggregations.Your relationship structure is:
Append1 (Many) → BridgeTable (Many)
BridgeTable (Many) → Teamsize (One)
When you select a Client, it works fine because Teamsize aggregates at the client level.
But when you select a Project, the Many-to-Many relationship does not pass filters correctly to Teamsize, causing it to always return the Client-Level total.
Try below:
Option 1: Use TREATAS in a Measure
Instead of relying only on relationships, try using TREATAS in a measure to enforce filtering at the project level.DAX
TeamSizeMeasure = VAR SelectedProjects = VALUES(Append1[ProjectID]) RETURN CALCULATE( SUM(Teamsize[TeamSizeCount]), TREATAS(SelectedProjects, Teamsize[ProjectID]) )Option 2: Change Relationship Type
Try Many-to-One between Append1 → BridgeTable if possible.If you need a Many-to-Many relationship, ensure bidirectional filtering is enabled (instead of single-directional).
Steps to enable bidirectional filtering:
Go to Model View in Power BI.
Select the relationship between Append1 and BridgeTable.
Change the filter direction to Both.
Test if selecting a project updates the count dynamically
If the above methods don’t work, create a helper table that maps Projects to Clients, ensuring a more structured Many-to-One relationship.
Please mark this post as solution if it hepls you. Appreciate Kudos.
4 Replies
Hi Shahnaz_Fathima ,
Your issue is happening because of the Many-to-Many (M:M) relationship between Append1 and BridgeTable. In such cases, filtering behavior doesn't always work as expected.
Why is the project selection not affecting the count?
You have a Many-to-Many (M:M) relationship between Append1 and BridgeTable, which can sometimes cause unexpected aggregations.Your relationship structure is:
Append1 (Many) → BridgeTable (Many)
BridgeTable (Many) → Teamsize (One)
When you select a Client, it works fine because Teamsize aggregates at the client level.
But when you select a Project, the Many-to-Many relationship does not pass filters correctly to Teamsize, causing it to always return the Client-Level total.
Try below:
Option 1: Use TREATAS in a Measure
Instead of relying only on relationships, try using TREATAS in a measure to enforce filtering at the project level.DAX
TeamSizeMeasure = VAR SelectedProjects = VALUES(Append1[ProjectID]) RETURN CALCULATE( SUM(Teamsize[TeamSizeCount]), TREATAS(SelectedProjects, Teamsize[ProjectID]) )Option 2: Change Relationship Type
Try Many-to-One between Append1 → BridgeTable if possible.If you need a Many-to-Many relationship, ensure bidirectional filtering is enabled (instead of single-directional).
Steps to enable bidirectional filtering:
Go to Model View in Power BI.
Select the relationship between Append1 and BridgeTable.
Change the filter direction to Both.
Test if selecting a project updates the count dynamically
If the above methods don’t work, create a helper table that maps Projects to Clients, ensuring a more structured Many-to-One relationship.
Please mark this post as solution if it hepls you. Appreciate Kudos.
Hi Shahnaz_Fathima ,
Thanks FarhanJeelani for Addressing the issue.
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Chaithra E. Hi Shahnaz_Fathima ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra. Hi Shahnaz_Fathima ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E.
