Values dissapear when adding information from other (linked) table

[Anonymous]

Hi all,

I have a purchase table (date - item - quantity - customer - price - ..) and a product table (item - weight - color - ..).
Both tables are linked through Item.

I have a table where I see all the information from the purchase table. When I want to add some product information (weight, color, ..), all values dissapear and I have an empty table (with just the column headers).
When I activate 'Show items with no data', the item column shows all the items, but all the other columns remain empty. 
The product information table is still in progress so, although all items are already in the table, not all items have already all fiels (product information) completed. So it occurs that, besides item, all other fields are empty.

The strange part is also that, although the created table (item - weight color - quantity - price) is empty (only Item is completed), I have a total at the end of the columns (quantity, price) that corresponds with the total quantity and total price.
So Power BI know the values, can create the sum at the end of the table, but doens't show them?

Many thanks.

[123abc]

It seems like you're encountering an issue in Power BI where when you try to combine data from your purchase table and product table using a common key (in this case, the "Item" column), some of the values disappear. This is a common challenge when working with linked or related tables in Power BI.

Here are some potential reasons and solutions for the issue you're facing:

1. Data Model Relationships: Ensure that you have correctly defined relationships between the purchase table and the product table based on the "Item" column. In Power BI, you need to set up relationships to combine data from multiple tables properly. Verify that you have established a one-to-many relationship between the tables, with the "Item" column as the linking field.

2. Cardinality: Check the cardinality of the relationship. In most cases, it should be "Single" on the product table side and "Many" on the purchase table side because one product can be associated with multiple purchases.

3. Cross-Filter Direction: Make sure you have set the cross-filter direction correctly. It's often a good practice to set it to both in order to allow filtering in both directions.

4. Data Completeness: You mentioned that not all products have all fields (product information) completed. If there are missing values in the product table for some items, it can result in blanks when combining data. You may want to ensure that there are default values or placeholders for missing information.

5. Aggregation: If you are using aggregation functions in your table (e.g., SUM, COUNT), ensure that they are set up correctly. Power BI may still calculate totals based on the relationships even if individual values are not visible in the table.

6. Filters and Slicers: Check if you have any filters or slicers applied to your report that might be affecting the visibility of data. Filters can hide data that doesn't meet the filter criteria.

7. Data Type Mismatch: Verify that the "Item" column in both tables has the same data type. A data type mismatch can cause issues with data retrieval.

8. Visualizations: Review the visualizations you are using. Different visualizations may have specific requirements and behaviors. Ensure that you are using the appropriate visualization for your data.

9. Data Modeling Errors: Check for any circular references or errors in your data model that could be causing unexpected behavior.

10. Refresh Data: Sometimes, issues can be resolved by refreshing the data in Power BI. Ensure that your data sources are up to date.

If you've checked all these aspects and the issue persists, you may want to provide more specific details about your data model and how you are creating the table in Power BI. Additionally, examining the Power BI error logs or diagnostic information can often provide clues about what might be going wrong.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Anonymous]

Hi 123abc,

I checked all your comments:

1. Data Model Relationships: Ensure that you have correctly defined relationships between the purchase table and the product table based on the "Item" column. In Power BI, you need to set up relationships to combine data from multiple tables properly. Verify that you have established a one-to-many relationship between the tables, with the "Item" column as the linking field.
   Relationship is based on the 'Item' column, with a many-to-many relationship. Doesn't succeed in a single-to-many relationship because both tables can have more lines for the same Item (Item can have several orders/purchases, Item can have 2, 3, .. rows based on how many different components).

2. Cardinality: Check the cardinality of the relationship. In most cases, it should be "Single" on the product table side and "Many" on the purchase table side because one product can be associated with multiple purchases.
   See point 1

3. Cross-Filter Direction: Make sure you have set the cross-filter direction correctly. It's often a good practice to set it to both in order to allow filtering in both directions.
   Activated

4. Data Completeness: You mentioned that not all products have all fields (product information) completed. If there are missing values in the product table for some items, it can result in blanks when combining data. You may want to ensure that there are default values or placeholders for missing information.
   There are no default values (besides blanco). My product data file is one with several columns and if certain column is not 'available' on the product, it remains empty.

5. Aggregation: If you are using aggregation functions in your table (e.g., SUM, COUNT), ensure that they are set up correctly. Power BI may still calculate totals based on the relationships even if individual values are not visible in the table.
   Ok

6. Filters and Slicers: Check if you have any filters or slicers applied to your report that might be affecting the visibility of data. Filters can hide data that doesn't meet the filter criteria.
   Only filters active based on the visual I select on the other sheet. But in this case it's just a filter on year, or customer, or ...

7. Data Type Mismatch: Verify that the "Item" column in both tables has the same data type. A data type mismatch can cause issues with data retrieval.
   Both Items have the same data type (text).

8. Visualizations: Review the visualizations you are using. Different visualizations may have specific requirements and behaviors. Ensure that you are using the appropriate visualization for your data.
   Visualization is just a simple table, nothing spectacular.

9. Data Modeling Errors: Check for any circular references or errors in your data model that could be causing unexpected behavior.
   Negative

10. Refresh Data: Sometimes, issues can be resolved by refreshing the data in Power BI. Ensure that your data sources are up to date.
    Refreshed but issue remains.

PS: In my first 'set-up', my product data (Fabric_Data) is a table with, besides columns with specific technical information, also a column for each possible component. If the component is used in the specific item, there is a percentage shown.
As I have a lot of possible components (>30), this made my report very 'cluttered'. And I also have to create measures for each component (weightA (component A) = % (component A) * weight, weightB (component B) = % (component B) * weight, ...). That's why I change the table in power query, and the part with the components are transformed to a list, so I have 1 column (= components) and then just a line for every component that is used on the specific item:
Item 1 - component A - 10%
Item 1 - component B - 90%
Item 2 - component A - 100%
Item 3 - component C - 50%
Item 3 - component D - 50%

...

Regards

[Anonymous]

After further investigating, it looks like my issue has something to do with the issue in my other topic (calculating weight with measures on 2 tables).

In my 'older' version of the report (where I didn't transform all the composition-columns to 1 column), the above issue is not present. I have my table with product data and purchase data combined, and for the items with no product data, I only see the Item and the purchase data (quantity/turnover).