Hi @Anonymous ,
A many-to-many relationship should be avoided where possible, especially for clarity in data modeling. Instead, the approach should be structured with a one-to-many relationship between CodeTable and both Stock and Used.
Start by loading both Stock and Used tables into Power BI. To ensure proper relationships, create a bridge table (CodeTable) containing unique codes:
CodeTable = DISTINCT( UNION( SELECTCOLUMNS(Stock, "Code", Stock[Code]), SELECTCOLUMNS(Used, "Code", Used[Code]) ) )
Then, establish one-to-many relationships:
CodeTable[Code] → Stock[Code]
CodeTable[Code] → Used[Code]
This setup ensures that filtering through CodeTable affects both Stock and Used tables correctly.
Now, create DAX measures to calculate the required values for the matrix. The Used Count measure counts how many times each code appears in the Used table:
Used Count = COUNTROWS(Used)
Similarly, the Stock Count measure calculates the occurrences of each code in the Stock table:
Overused = MAX(0, [Used Count] - [Stock Count])
For overstocked items (codes stocked more than used), reverse the calculation:
Overstock = MAX(0, [Stock Count] - [Used Count])
To ensure that the team and sub-team filtering is respected, use CALCULATE to keep the necessary context:
Used Count by Team = CALCULATE( COUNTROWS( Used ), ALLEXCEPT( Used, Used[Team], Used[Sub_team], Used[Code] ) )
Similarly, apply the same logic for the stock count:
Stock Count by Team = CALCULATE( COUNTROWS( Stock ), ALLEXCEPT( Stock, Stock[Team], Stock[Code] ) )
Use these team-based calculations to ensure that Overused and Overstock calculations remain accurate:
Overused by Team = MAX(0, [Used Count by Team] - [Stock Count by Team])
Overstock by Team = MAX(0, [Stock Count by Team] - [Used Count by Team])
Finally, in the matrix visual, set:
Rows: Team → Sub_team → Code
Values: Used Count, Stock Count, Overused, Overstock
This structure correctly compares used and stocked codes, highlights any overuse or overstock situations, and avoids unnecessary many-to-many relationships.
