Power BI cannot sort hierarchical levels with repeating labels across multiple hierarchies

[kkjakarta]

Hey there,
I’m building a Power BI data model on top of a Snowflake database.
The data model is based on a hierarchical dimension tables (for example, dimension: PRODUCT) that are structured as follows:

• Keys are given as ELEMENT_ID

• Multiple parallel hierarchies are distinguished by a HIERARCHY_ID.

• Each element within a hierarchy has a sequential RANK_ID that defines its position.

• Every level is flattened into separate columns, such as:

  • PRODUCT_01, PRODUCT_01_NAME_GERMAN

  • PRODUCT_02, PRODUCT_02_NAME_GERMAN

  • …

  • up to PRODUCT_13

Problem description:

Power BI refuses to sort a column (e.g. PRODUCT_02_NAME_GERMAN) by its corresponding order column (PRODUCT_02_ORDER) because the same text label occurs in multiple hierarchies with different order numbers.

Example:

ELEMENT_ID PRODUCT_01_NAME_GERMAN PRODUCT_02_NAME_GERMAN

XXX	ABC	International
YYY	ABC LFL	International
ZZZ	ABC WT	International

Power BI throws the classic error:

“The column you are trying to sort by contains duplicate values for the same key.”

However, these duplicates are intentional, because the same product subcategory (“International”) can appear under multiple top-level hierarchies.

I thought PBI would have something like a lookup that would calculate the Name for each Level that is looked upon, but it in a matrix, when i selected the name to be seen, it expanded to the highest level.

---

What I already tried

• Creating DENSE_RANK or ROW_NUMBER logic per level — works within one hierarchy,
  but Power BI still sees duplicate text labels across hierarchies.

• Created sort Columns for each Level PRODUCT_01_SORT, PRODUCT_02_SORT, ... — creating

• Partitioning ranks by HIERARCHY_ID — creates correct numbers but still breaks sort relationships.

• Creating concatenated sort keys (e.g. "PRODUCT_01_NAME_GERMAN > PRODUCT_02_NAME_GERMAN")
  — helps distinguish hierarchies, but Power BI cannot handle a clean visual sort by that key.

---

💭 Goal

I need a global, hierarchy-aware sorting logic that:

1. Orders all elements according to HIERARCHY_ID and RANK_ID (the same as in the source system “CREST”) over parallel Hierarchies

2. Still allows Power BI to sort each hierarchy level column (PRODUCT_0X_NAME_GERMAN) properly

Essentially, Power BI should render the hierarchy in the same top-down order as in CREST, even if the same names appear multiple times in different hierarchies.

---

Question

How can I design the model or the SQL view so that Power BI:

• Respects the hierarchy order across all hierarchies,

• Does not fail due to repeating labels with different sort orders,

• And still allows per-level display and sorting in visuals (matrix or hierarchy view)?

[lbendlin]

Parent-Child Hierarchies with multiple parents in Power BI with Power Query

tldr; you need to clone the child nodes in such scenarios.

[OwenAuger]

HI @kkjakarta 

It sounds as though this can be solved using the Group By Columns property.

One possible solution would be:

1. Introduce a Hierarchy/Name key column that uniquely identifie each Hiearchy/Name combination, then set PRODUCT_0X_NAME_GERMAN (and similar) column's Group By Columns property to that column.
2. Then set the Sort By Column property of PRODUCT_0X_NAME_GERMAN to PRODUCT_0X_ORDER. This cannot be done directly in the Power BI Desktop interface so must be done in Tabular Editor or TMDL view.

Have a look at this article for further detail on setting this up (requires Tabular Editor or a TMDL script).

https://www.sqlbi.com/articles/understanding-group-by-columns-in-power-bi/