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

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:

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)?