Good morning all,
I have to tables related by a many-to-many relationship, and I'm having a hard time coming up with an audit within Power BI. Sorry in advance if this is a common question: I wasn't able to find a solution with the search function. Thanks.
Tables
-Actual Inventory Table: Contains a list of Store IDs in Column A and and Product IDs of products we've deployed at each store in Column B. There are typically 3-4 Product IDs deployed per Store ID.
-Inventory Master Table: What should be deployed at each store. Constantly being updated. Same makeup as the prior table.
The point of this excerise is to determine stores where we have deployed the wrong products. I need to find the best way to create an audit in Power BI that does the following:
-For each row in the [Actual Inventory Table], filter the [Inventory Master Table] by each row's Store ID.
-Once filtered, compare the single [Actual Inventory Table] Product ID of a given row to the Product ID of the 3-4 rows in the filtered [Inventory Master Table].
-If there is a match, "Yes" else "No"
