How to use Dynamics 365 SCM Product Categories in PowerBI

Recently we had the requirement to use the product categories from Dynamics 365 Supply Chain Management in a PowerBI report as well. It should be used to filter products by categories and sub-categories. We were using Data Lake export in D365 FSCM.

This is how product category hierarchy looks like in Dynamics 365 SCM:

This is how it should look in PowerBI:

Data sources

To build a product category model in PowerBI some tables need to be exported:

• EcoResCategoryHierarchy
• EcoResCategory
• EcoResProductCategory
• EcoResProduct

Load and model the category hierarchy data

Start with EcoResCategory table. I’d recommend to remove all unnecessary fields and keep at least RecID, Name and ParentCategory.

The field ParentCategory is a reference to the RecID of the parent line. In theory this would be sufficient to build the hierarchy in PowerBI. In practice we want to create the hierarchy via the name, not the RecID. Create a copy of the table and remove all fields except the Name and RecID. I call the new table EcoResCategoryName:

Create an outer join from the original table to the new EcoResCategoryName table via the ParentCategory to the RecID.

As result the Name of parent is now part of the table. For example the parent of PC&Tablet is Lenovo Sales. and the parent of Server as well as the parent of Storage is Server & Storage.

Safe and load the data.

Build the Hierarchy

In PowerBI you can use the DAX formula PATHITEM to identify a certain element within a hierarchy. You have to provide the PATH (i.e. Name and ParentName) how the hierarchy is linked. For example to get the first level hierarchy element use the following code:

Level1 = PATHITEM(PATH(EcoResCategory[Name],EcoResCategory[ParentName]),1)

In my example “Lenovo Sales” is a root element for my hierarchy. The other root elements like “Neue Kategorie” and “Elektro Geräte” belong to another hierarchy (see CategoryHierarchy field). My hierarchy has 3 levels, therefore I create 3 additional fields called Level1, Level2, Level3.

Level2 = PATHITEM(PATH(EcoResCategory[Name],EcoResCategory[ParentName]),2)
Level3 = PATHITEM(PATH(EcoResCategory[Name],EcoResCategory[ParentName]),3)

To create the hierarchy in PowerBI right click on the Level1 field and select create hierarchy. Right click on Level 2 and choose “Add to hierarchy”. Do the same with Level 3. Your hierarchy should look like this:

Create the PowerBI data model

To implement a report like in Dynamics 365 SCM, load 3 additional tables:

• EcoResCategoryHierarchy
• EcoResProductCategory
• EcoResProduct

Link the EcoResCategoryHierarchy.RecId to EcoResCategory.CategoryHierarchy
Link the EcoResCategory.RecId to EcoResProductCategory.Category
Link the EcoResProductCategory.Product to EcoResProduct.RecId

In my example the relations look like this:

Add visuals to the report

I’ve added 3 visuals to the report. First, a filter element to choose from the EcoResCategoryHierarchy. For example the “Lenovo” hierarchy consisting of Laptops, Servers and Storage products.

Next a filter element for the hierarchy that was created earlier. PowerBI presents the hierarchy in a drop down tree. To avoid “Blank” elements in the hierarchy create a visual level filter and exclude the blanks

My third visual is a table containing the product. The products get filtered based on the selection of the hierarchy elements.