Allergen Storage Verification Project

[IceCreamInv]

To the good members of the Power BI community - 

I'm fairly new to Power BI, with no formal training in it. I'm half-way decent at Excel, but only due to experience using it. Some of my formulas may be a bit convoluted, since I pieced them together as needed. Feel free to suggest simpler formulas. 

I'm trying to develop a tool that I can use to call out any on hand items that are stored incorrectly due to mismatching allergens or GMO status. 

For those who haven't worked in food manufacturing (using US standards), here's a quick explanation using 7 items that I will use as an example: 

• Item A is a building block ingredient that has no major allergens. 
• Item B contains Dairy.
• Item C contains Dairy and Wheat. 
• Item D contains Dairy and Peanuts.
• Item E contains Wheat and Peanuts
• Item F is Certified Non-GMO. 
• Item G is Certified Non-GMO and contains Dairy.

These items are stored in a warehouse setting where there are multiple levels of storage racking. For food safety, items with allergens must be stored in a way that new new allergens are introduced by the items stored above it, in case of damaged packaging or spills. For example, Item B can be stored directly above Item C, but not vice versa, since C could introduce Wheat to Item B if damaged or spilled. Neither item can be stored above or below item E because Peanuts are a completely different allergen and it does not contain Dairy. 
Non-GMO status is something similar, but it acts more like the opposite of an allergen, since it has to be stored above GMO items. Using these items, Item F must be stored above all other items, including item A, since item A does not call out its GMO status. Item G could be stored above Items, B, C, and D, since all of them contain Dairy. It is not compatible with Items A and E. 

Feel free to ask for clarification if needed on storage rules if needed. 

Using our company's Active Ingredient List, I am able to pull the allergens, item lists, and the bin locations into Power BI using the Power Query. However, I haven't been able to figure out how to build the logic to verify storage. Please note: I am including "Non-GMO" status as an allergen for simpler explanation.

In Excel, I was able to create some logic to show storage relationships between allergens using the process below:

1. I created a table with each of the allergen combinations down the side, with each individual allergen across the top. Currently, I have 13 different allergens and 42 different allergen combinations between them. 
2. Using the formula "=IF(ISNUMBER(SEARCH(*Column Title*,*Row Title*)),0,1)" in each cell below an allergen title, I am able to create a binary Allergen ID for each Allergen Combination with "=CONCAT(1,Allergen_List[*First Allergen*:*Last Allergen*])", i.e., 11000101000101. I added a 1 to the beginning of the ID because of the later steps. This also helps if the allergen listing does not have them stated in the same order. Almond/Milk items have the same ID as Milk/Almond. 
   • Here is a Snapshot of the top of the table. 
   • 
3. I created the compatibility table by listing each allergen combination across the top and side of a table. In the corresponding cells, I've used this formula: (cell names are explained with *Cell Description*)
   • "=IF(*Vertical Allergen ID*=*Horizontal Allergen ID*,"Same Allergen",IF(*Vertical Allergen Name* ="Non-GMO","Over",IF(*Horizontal Allergen Name*="Non-GMO","Under",IF(ISNUMBER(SEARCH("9",TEXT(*Vertical Allergen ID*-*Horizontal Allergen ID*,"0000000000000"))),"Non-Compatible",IF((*Horizontal Allergen ID*-*Vertical Allergen ID*)>0,"Under",IF((*Horizontal Allergen ID*-*Vertical Allergen ID*)<0,"Over","Same Allergens"))))))
     • Note: Allergen Name Example: "Milk, Soy, Almond"
     • Note: Allergen ID Example: 11100001000100
       
   • Basically, if I subtract the two Allergen IDs and anywhere in the number a "9" is present, the two items are non-compatible. If the number is positive, the vertical allergen must be stored under the horizontal allergen. If the number is negative, the vertical number must be stored above the horizontal allergen. 
   • Here is a snapshot of the top corner of the table. 
   • 

Question 1: The issue with this is that I haven't been able to have it be dynamic as new allergens or allergen combinations arrive. How can I, from a list of allergen combinations, pull out the unique allergens, use them to create column headers in order to create the allergen IDs? Then, how do I create an allergen compatibility matrix using new allergen combinations? 

After that is created, I know how to assign the allergen IDs to each item code that we have in our inventory systems. Then, I can list which allergens are in which bins using LOOKUPVALUE in DAX/Custom Columns in Power BI. 

Question 2: How do I compare bin locations with the spots above and below each one and allow for it to flag for any potential issues? How do I account for empty bins? 

• Here's how we label our bin locations:
• 

Let me know what you can do! Feel free to ask questions. I will try to respond in a timely manner. 

Thank you in advance!

[Mahesh0016]

@IceCreamInv  I hope this helps you. Thank You.

Your goal is to use Power BI to create a tool for verifying the correct storage of food items based on allergen and GMO status. To achieve this, you'll need to:

1. Create a dynamic allergen compatibility matrix.
2. Assign allergen IDs to each item code in your inventory.
3. Compare bin locations with the items above and below them to flag potential issues, while also accounting for empty bins.

Let's break down each of these tasks:

### Task 1: Create a Dynamic Allergen Compatibility Matrix

To create a dynamic allergen compatibility matrix in Power BI, you can follow these steps:

1. **Data Modeling**:
- Import your allergen combinations data into Power BI.
- Create a table that contains all unique allergens found in your data.

2. **Generate Allergen IDs**:
- Use Power Query (M) or Power BI's Power Query Editor to create a new column that generates the allergen IDs for each combination of allergens.
- You can concatenate allergen codes alphabetically or based on a predefined order.

3. **Build the Compatibility Matrix**:
- Create a new table or matrix visual.
- Use DAX measures or calculated columns to populate the matrix cells based on your compatibility logic.
- For example, you can use SWITCH or IF statements to determine compatibility.

4. **Handle New Allergens**:
- For handling new allergens or combinations, ensure that your data source is regularly updated.
- When new data is imported, refresh the data model to include the new allergens.

### Task 2: Assign Allergen IDs to Inventory Items

Once you have the dynamic allergen compatibility matrix, you can assign allergen IDs to each item code in your inventory using Power BI's DAX functions or Power Query.

1. **Data Modeling**:
- Import your inventory data into Power BI, which includes the allergen information for each item.

2. **Assign Allergen IDs**:
- Create a calculated column or DAX measure that looks up the allergen combination for each item and assigns the corresponding allergen ID.

### Task 3: Compare Bin Locations for Potential Issues

To compare bin locations for potential issues based on allergen and GMO status, you'll need to consider the storage rules you mentioned and create a logic to check these rules.

1. **Data Modeling**:
- Import your bin locations data into Power BI, including the item codes stored in each bin.

2. **Logic for Comparison**:
- Create DAX measures or calculated columns to compare the allergen IDs of items in adjacent bins based on your storage rules.
- Account for empty bins by checking if a bin is empty before applying compatibility checks.

3. **Flagging Issues**:
- Use conditional formatting or calculated columns to flag bins where potential issues exist.
- Create visuals that highlight these flagged bins for easy identification.

4. **Regular Review**:
- Ensure that your data source for bin locations is regularly updated.
- Regularly refresh the Power BI report to reflect any changes in bin contents or allergen information.

By following these steps, you can build a dynamic tool in Power BI that helps you identify and flag potential storage issues based on allergen and GMO status. Remember to maintain and update your data sources and Power BI report as needed to keep the tool accurate and effective.