How to create hierarchy when Hierarchy levels are read from a database table

[Power-Geek]

Hello, 

I need to read the hierarchy levels from a database table and can be different for different customer. For example

AttributeId	AttributeHierarchyLevel	Attribute
3	1	DEPARTMENT
8	2	SUB_DEPARTMENT
2	3	CATEGORY

There is a table that has records for the products and their attributes. As there are three levels of hierarchy, there are three records per product. For example the product 2784580201 is in the Departmen General Merchandise, Sub-department of Novelty Items and Category of Novelty. 

The attributes are read from the database and the hierarchy needs to be created dynamically based on what attributes and levels are retrieved from the database.

ProductId	AttributeId	Attribute	AttributeValue	AttributeHierarchyLevel	AttDesc
2784580201	3	DEPARTMENT	130	1	GENERAL MERCHANDISE
2784580201	8	SUB_DEPARTMENT	9340	2	NOVELTY ITEMS
2784580201	2	CATEGORY	931194	3	NOVELTY
5751113105	3	DEPARTMENT	100	1	GROCERY
5751113105	8	SUB_DEPARTMENT	9135	2	PASTRIES AND CAKES
5751113105	2	CATEGORY	911235	3	PASTRIES
989770893	3	DEPARTMENT	130	1	GENERAL MERCHANDISE
989770893	8	SUB_DEPARTMENT	9375	2	TOYS KNIVES CARDS
989770893	2	CATEGORY	931380	3	TOYS
23202027	3	DEPARTMENT	130	1	GENERAL MERCHANDISE
23202027	8	SUB_DEPARTMENT	9340	2	NOVELTY ITEMS

Then I have a usual sales table for the products. 

How can I create hierarchy based on the hierarchy levels read from the database table? Can someone point me to any documents/solutions?

[v-jingzhang]

Hi @Power-Geek 

Is it possible to create a relationship between Hierarchy Level table and Product table on AttributeId or AttributeHierarchyLevel column? Then use RELATED() function to get the value you want from the Hierarchy Level table into the other table.

Or since every product has three levels, combine product and level columns to create a unique ID column, for example, 'Table'[ProductId] & "-" & 'Table'[AttributeHierarchyLevel]. 

Best Regards,
Community Support Team _ Jing

[Power-Geek]

Thank you for the reply v-jingzhang. 

The hierarchy levels are going to be variable ( there could be 3, 4, or 5 levels based on customer) and need to be dealt with dynamically. (And I am brand new to PBi)

I created a table like below and the pivoted on the Attribute column.

ProductId	Attribute	AttDesc
32784580201	Column-1	GENERAL MERCHANDISE
32784580201	Column-2	NOVELTY ITEMS
32784580201	Column-3	NOVELTY
35751113105	Column-1	GROCERY
35751113105	Column-2	PASTRIES
35751113105	Column-3	PASTRIES
3989770893	Column-1	GENERAL MERCHANDISE
3989770893	Column-2	TOYS KNIVES CARDS
3989770893	Column-3	TOYS
4023202027	Column-1	GENERAL MERCHANDISE
4023202027	Column-2	NOVELTY ITEMS
4023202027	Column-3	NOVELTY

 Now my table has columns Product_id, Column-1, column-2 and column-3.

Column-1,column-2 and column-3 represent the hierarchies (Department, Sub-dept, Category). 

When creating a hierarchy slicer, I need to pull Column-1, column-2 and column-3 ( to column-n) and Product_id onto the "Field" area for the visual. Is there a way that all the columns in a table be pulled on the "Field" for the slicer visual without knowing the column names??