Bringing Excel file in with alternative hierarchies. Please read

[iGWizard]

Hi,

I've built a report to display the performance of various categories throughout the business. 

This report displays GB categories and works perfectly.

I now need to duplicate the report but display Ireland categories. The only issue is, Ireland use slightly different hierarchies. They use the same categories but different products make up each category.

For example,

GB hierarchies may be as follows

Category A - Product A, B, C

Category B - Product D

Ireland hierarchies may be.

Category A - Product B, C, D

Category B - Product A

I've decided its probably possible to create a simple excel file (2 columns) one with category names, the other with the product(s) that fall within.

I would then bring in the excel file to the pbix file. Create a relationship between the Product table and the new excel file.

Then create a filter that allows me to select the Ireland categories containing the ireland hierarchies.

Does this sound like the right thing to do?

Thanks

[Anonymous]

Hi @iGWizard ,

Did you solve your problem? If so, please mark FarhanJeelani 's answer as the solution. If not, could you tell me what problem you encountered?

Best Regards,
Wenbin Zhou

[FarhanJeelani]

Hi @iGWizard ,

Yes, your approach is logical and should work effectively. Here's a step-by-step breakdown of why and how this method can solve the issue:

1. Excel File Creation:
   By creating an Excel file with two columns (Category Name and Product), you are establishing a mapping between categories and their corresponding products for Ireland. This file essentially serves as a lookup table that can easily be updated or modified in the future.

2. Importing the File into Power BI:
   Bringing this file into your Power BI (pbix) file is straightforward. Power BI supports Excel files, so this step is seamless. Ensure that your Excel file has headers (e.g., Category and Product) for clarity and easier relationship building.

3. Creating a Relationship:
   Establishing a relationship between your Product table and the new Excel table is the right way to ensure that Ireland-specific hierarchies are correctly interpreted. Make sure the relationship is set based on the Product column.

4. Filtering Ireland Categories:
   Using the new Excel table as a filter, you can now separate Ireland hierarchies from GB hierarchies by applying filters in your visualizations or measures. The filter will ensure that the data displayed corresponds to Ireland's categorizations.

5. Validation:
   After setting up the relationship, validate the report by checking a few categories to ensure the Ireland hierarchies are displaying as expected. If issues arise, double-check the data in your Excel file and the relationship in the model.

Additional Tips:

• Dynamic Switching (Optional): If you frequently switch between GB and Ireland hierarchies, consider adding a slicer in Power BI to toggle between GB and Ireland using a "Region" column in your Excel file. Update your file to include a "Region" column, where GB and Ireland hierarchies are defined separately.

• Data Transformation: Use Power Query to clean or transform the Excel data before loading it into the Power BI model if needed.

• Performance Considerations: Ensure your Product table and the new Excel table don’t have duplicate product entries (unless intentional), as it can cause ambiguous relationships.

This setup should achieve the flexibility you need to display Ireland's specific hierarchies while maintaining a clean and efficient data model.