Forum Discussion
Data modelling
Hey Analyst26 ,
It sounds like you're on the right track by linking the tables via Product Code. However, the issue might be due to how the relationships are set up or how you're using the fields in your matrix. A few things to check:
Relationship Direction & Cardinality: Ensure the relationship between your tables is active and set correctly (typically one-to-many with the dimension table on the "one" side).
Common Dimension Table: Instead of linking the two fact tables directly, consider creating a separate dimension table (e.g., a Date or Product table) and relate both spreadsheets to that. This enables proper filtering across both.
Matrix Setup: If you're using columns from one table only in the matrix (like values from one table and not the other), the blank values may appear. Try using fields from the shared dimension tables (like Product or Date) for rows/columns in the matrix.
Measure Creation: Create separate measures for the financial figures from each table using DAX (e.g., SUM(Table1[Value]), SUM(Table2[Value])) and then bring those into the matrix.
For Detailed Information:
Create and manage relationships in Power BI
Model relationships in Power BI Desktop
Best practices for designing Power BI data models
Create a matrix visual in Power BI
DAX basics in Power BI Desktop
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Hi Nasif
Thanks so much for your input. I have created a dimension table with company name, department and product code. Each of this 3 has a unique code so I've also done a merged column of these 3 codes. Some of the data in the merged column does duplicate. Due to this I've linked the two other spreadsheets to the merged column and created a many to many relationship - it's recognising the values of one set of figures from one excel dataset but still not the other!
Hey Analyst26 ,
You are very welcome. Thanks for the update, sounds like you are almost there! 😊
The issue now likely stems from the many-to-many relationship using the merged column. Many-to-many relationships can behave unpredictably in filtering, especially when used across multiple fact tables. My Recommendations:
1. Create Separate Dimension Tables: Instead of using a merged column for a combined key (Company + Department + Product Code), try splitting it out into separate dimension tables for:
Product (unique Product Code)
Department (unique Department Code)
Company (unique Company Code)
Then, relate both spreadsheets (fact tables) to these separate dimensions using one-to-many relationships. This structure is much more stable and improves filtering behavior in visuals like the matrix.
2. Use Measures Instead of Raw Columns: If you haven’t already, create measures for your values in each table, such as:
TotalValue_Table1 = SUM(Table1[FinancialValue]) TotalValue_Table2 = SUM(Table2[FinancialValue])
Use these measures in your matrix instead of dragging raw columns.
3. Avoid Using the Merged Column as a Link: Power BI doesn’t always filter many-to-many relationships well, especially with non-unique merged keys. A star schema (with separate dimension tables) is more reliable.
4. Check for Missing Matches: In one of the spreadsheets, the merged key values might not exactly match the other table or the dimension. Even extra spaces or different text casing can break joins. Use TRIM, CLEAN, or Power Query transformations to clean the values before loading.
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif AzamHi Nasif_Azam - Perhaps it would be worth adding a disclaimer to your answers advising that "This is pasted from ChatGPT, and this can sometimes provide incorrect answers".
Analyst26 - If you could take a sample of both spreadsheets and change the sensitive info to mask the data, and then share here, it will be easier to answer. It sounds to me like the data may need to be merged, not joined, but I may be misinterpreting your question.
