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:

1. 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).

2. 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.

3. 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.

4. 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