Forum Discussion
Data modelling
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
Hi 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.
Hi KNP ,
Since Analyst26 mentioned he unable to share sample data due to confidentiality. I shared an approach based on common best practices that have worked well in similar scenarios. Additionally I refer the sources that are helpful for data modeling and I studied from it to provide a solution.
When dealing with multiple data tables that share common fields like Product, Department, and Company, it is often more effective to avoid many-to-many relationships. Instead, building separate dimension tables for each of these fields and connecting them through one-to-many relationships usually provides more consistent and reliable filtering in visuals like matrices.
Also, creating DAX measures instead of using raw columns directly helps avoid issues with blanks values.
Best Regards,
Nasif AzamHi Nasif_Azam,
It's not the content of the answer I have a problem with. It's the fact that it is clearly copy/pasted directly from ChatGPT and there isn't enough information to come to those conclusions. This is why I asked Analyst26 to mask any sensitive information prior to sharing his datasets. We simple don't have enough information to provide a good answer.
I respect that you're taking the time to try and help people Nasif_Azam, I think you could do better than pasting from a system that often gets things wrong and you've clearly not been able to validate your answer with the information available.