Allergen Storage Verification Project

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.