Data modelling

[Analyst26]

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 figure type. I'm trying to create a matrix that brings in figures from both spreadsheets. I've linked the tables by product in the modelling tab however matrix is still only recognising the figures from one of the spreadsheets and showing the others as 0. Thanks 

[v-vpabbu]

Hi @Analyst26,

It looks like the issue is due to a relationship problem in the data model. Although you’ve connected the two spreadsheets by Product Code, fields like Month and Department used in the matrix may not be properly related across both tables. Because of this, Power BI is unable to filter both datasets correctly, resulting in 0s for one of them.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[danextian]

Hi @Analyst26 

You will need to create separate dimension tables for the common dimensions between the two and use measures to combine the values from both. These can be created either in DAX or M or loaded from the data source. Example DAX calc table:

Products =
VAR _tableA =
    SELECTCOLUMNS (
        TableA,
        "Product ID", TableA[Product ID],
        "Product Name", TableA[Product Name]
    )
VAR _tableB =
    SELECTCOLUMNS (
        TableB,
        "Product ID", TableB[Product ID],
        "Product Name", TableB[Product Name]
    )
RETURN
    DISTINCT ( UNION ( _tableA, _tableB ) )

Create a one to-many single direction relationship from Product ID in this table to Product ID/code columns of the other tables. Use this column in your visual - not from your fact table. Create a measure to combine the aggregation from both fact tables. Example:

Sales Table A and B =
SUM ( TableA[Sales] ) + SUM ( TableB[Sales] )

[Nasif_Azam]

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

[Analyst26]

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]

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

[KNP]

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.

[Nasif_Azam]

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 Azam

[KNP]

Hi @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.

[SundarRaj]

Hi @Analyst26,
Would you be able to share the dataset that you are working with? Preferably the output you are wishing for as well.
Thanks

[Analyst26]

Hi Sundar

I can't share company info unfortunately but really appreciate your response.