Forum Discussion

Analyst26's avatar
Analyst26
New Member
1 year ago

Data modelling

Hi   i have two separate spreadsheets that I've loaded into power bi. Some of the columns have the same info ie month, department and product code. The other columns contain a value for a financial...
Need Help
Analyst26's avatar
Analyst26
New Member
1 year ago

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! 

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

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 Azam