General "Why is it broken" question: Adding measure to table breaks relationship.

[PhilSmith]

Hey all,

This is a general question. When this happens, where do you look, what do you look for? The Scenario:
You have a table visual,  Category, Brand, and a couple fields from Products, all related tables.
Slicing works fine.
Then you add a measure, from Sales, and you break Category.
Category is your first coloum in the table, and after adding that measure, slicing Category changes the Category displayed
in the first coloum, but it doesn't change the products displayed.  It is disconnected from Products.  Choose Toys instead of Groceries, First column shows Toys, but the rest is still displaying Apples and Apple Sales. It's as if
Category is suddenly disconnected from everything else. As soon as you remove anything of Sales from the
table, it reverts back to working just fine.  

What do you look for? Where do you start looking when adding a measure to a table breaks an otherwise functional relationship?

I hope that makes sense.

Thanx

Phil

[v-yiruan-msft]

@TomMartens Thanks for your contribution on this thread.

Hi @PhilSmith ,

As previously mentioned by @TomMartens , the specifics of your data model remain unclear to us. This lack of information hinders our ability to accurately diagnose the issue and provide an effective solution. We kindly request that you share the necessary details as suggested, which will enable us to track and address the issue more efficiently.

Alternatively, you may wish to conduct a self-guided issue trace by referring to the following steps:

• Check Relationships: Ensure that the relationships between the 'Category', 'Brand', 'Products', and 'Sales' tables are correctly defined. Go to the data model view and verify that the relationships are correctly mapped and set to the appropriate cardinality (one-to-many, many-to-one, etc.).
• Cross-Filter Direction: Ensure that the cross-filter direction is set appropriately, especially if you have bi-directional filtering. Sometimes, a single-direction filter might not propagate the filter context correctly.
• Measure Logic: Review the DAX formula used in your measure. Ensure that it is correctly designed to respect the relationships and propagate the filter context as intended. For example., if your measure uses 'CALCULATE', check that it’s correctly referencing related tables and columns.

By following these steps, you might be able to identify and resolve the problem independently. We appreciate your understanding and cooperation in this matter. Please do not hesitate to reach out if you require further assistance. We’re here to help!

Best Regards

[TomMartens]

Hey @PhilSmith ,

I'm not able to understand your semantic model.

Consider creating a pbix file that contains sample data, but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive Google Drive, or Dropbox and share the link. If you are loading data from a spreadsheet instead of using the manual input method share the spreadsheet as well.

Nevertheless, make sure that you are using the columns from the dimension tables in the visuals and that you are using the dimension tables in the measure.

You also have to be aware that as soon as you are adding a measure to a visual, the suppression of empty rows kicks in. If you want to keep empty rows, activate the "Show items with no data" property: Show items with no data in Power BI - Power BI | Microsoft Learn

Regards,

Tom