I'm trying to resolve an issue I have with a many to many relationship.
I have a primary table that has about a million rows and then a secondary table that has just 76 rows.
The secondary table contains some financial targets split by month and branch. This table contains three columns that are identical in format to those in the primary data: Branch, Year, APE (think of it as months) and then has some financial values that I want to pull through. Note no values in the secondary table for the three common fields are not present in the primary data set and vice versa.
The join is currently set to use the Branch column and at a high level works perfectly. The problem I have is when I try to filter by APE, if I do this the filter only seems to apply to the primary dataset and the seconday sets values remain unchanged. If I bring in the same filter from the secondary source and apply the filter there then only the secondary calculations change.
If I change my join to APE, as opposed to Branch, I get the same issue, albeit for Branch this time.
Essentially whichever value I choose in the join works for both, but Power BI seems unable to apply that logic to the other identical columns.
I tried creating two "bridge" tables that allow many to one relationship, but the same issue persisted.
Is there a manual way to specify that both Branch and APE are to be used within the relationship?
