Data modelling

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

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

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] )

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

Hi Analyst26,

I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the solution so other community members facing similar issues can benefit too.
If not, don’t hesitate to reach out, we’re happy to keep working with you on this.

Regards,

Vinay Pabbu

Hi Analyst26 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Hi Analyst26 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. 

Thanks and regards