Inventory Report with a product dimension table

[Anonymous]

I'm using Power BI for work and the company holds all their data through SAP. 

There is a report that can be pulled from SAP that contains the inventory stock of our products. The products are unique values and each have their own product number. However the report contains the stock and stock only, no information about the product, the category it is attached to, or the profit center. 

In Power BI, I have my sales report that has a product dimension table, with all the mentioned columns above. Again our products each have their own product number code, but multiple products can fall under one category/product line. (i.e. rings and necklases both being accessories)

I'm trying to add in our inventory table from SAP and connect it to my dimension product table, so that when I filter inventory results, I can see it by the product line, category, etc. But of course I cannot create a relationship between the two tables because "one of the columns must have unique values".

Can anyone help me with this?

[Anonymous · Accepted solution]

Hi @Anonymous 

Based on what I have got, both Inventory table and Product dimension table have a product number column, so I think you can create a relatonship on this column. The relationship cross-filter direction can be single (from dimension product table to Inventory table) or both so that the columns from dimension table can filter data in the Inventory table. 

When you can't create a relationship, do you meet any error message? Is there an existing relationship which was created automatically between tables? Sometimes the auto-created relationship is not properly set so you can modify it or remove it then create a new one manually to meet your target. Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Otherwise if you want to bring some data from a table into another table based on matching columns with Power Query, you can use Merge queries feature. Merge queries overview - Power Query | Microsoft Learn

If this doesn't help, please provide more details to describe the problem you met and show your expected outcome with some sample data. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!