How to sort dax for dynamic hierarchies?

Hi Guys,

i am using this tutorial: https://www.youtube.com/watch?v=5G_xSJy5muo

in order to create dynamic hierarhies:

Dynamic Hierarchy = {
    ("BillingAccountId", NAMEOF('Fct_EA_AmortizedCosts'[BillingAccountId]), 0, "Billing Hierarchy"),
    ("BillingAccountName", NAMEOF('Fct_EA_AmortizedCosts'[BillingAccountName]), 1, "Billing Hierarchy"),
    ("BillingProfileId", NAMEOF('Fct_EA_AmortizedCosts'[BillingProfileId]), 2, "Billing Hierarchy"),
    ("BillingProfileName", NAMEOF('Fct_EA_AmortizedCosts'[BillingProfileName]), 3, "Billing Hierarchy"),
    ("InvoiceSectionId", NAMEOF('Fct_EA_AmortizedCosts'[InvoiceSectionId]), 4, "Billing Hierarchy"),
    ("InvoiceSectionName", NAMEOF('Fct_EA_AmortizedCosts'[InvoiceSectionName]), 5, "Billing Hierarchy"),
    ("SubscriptionId", NAMEOF('Fct_EA_AmortizedCosts'[SubscriptionId]), 6, "Subscription Hierarchy"),
    ("SubscriptionName", NAMEOF('Fct_EA_AmortizedCosts'[SubscriptionName]), 7, "Subscription Hierarchy"),
    ("ResourceGroup", NAMEOF('Fct_EA_AmortizedCosts'[ResourceGroup]), 8, "Subscription Hierarchy"),
    ("ResourceName", NAMEOF('Fct_EA_AmortizedCosts'[ResourceName]), 9, "Subscription Hierarchy"),
    ("ResourceType", NAMEOF('Fct_EA_AmortizedCosts'[ResourceType]), 10, "ResourceType Hierarchy"),
    ("ResourceType", NAMEOF('Fct_EA_AmortizedCosts'[MeterId]), 11, "ResourceType Hierarchy"),
    ("ResourceType", NAMEOF('Fct_EA_AmortizedCosts'[MeterCategory]), 12, "ResourceType Hierarchy"),
    ("ResourceType", NAMEOF('Fct_EA_AmortizedCosts'[MeterSubCategory]), 13, "ResourceType Hierarchy")
}

but the issue is that ResoureType is shown before Subcription because of alphabetical order. How to sort this table by my own terms (order should be like in dax above).

Thanks!
Jacek

Need Help

v-achippa
1 year ago
Hi jaryszek,

Both columns HierarchyGroup and Label must be sorted correctly for the hierarchical slicer to work. Please follow both steps below carefully:

Sort Label by SortOrder

Sort HierarchyGroup by a new column, go to Modeling --> New column and create this column:

GroupSortOrder =

SWITCH(

    TRUE(),

    'DynamicHierarchyTable'[HierarchyGroup] = "Billing Hierarchy", 1,

    'DynamicHierarchyTable'[HierarchyGroup] = "Subscription Hierarchy", 2,

    'DynamicHierarchyTable'[HierarchyGroup] = "ResourceType Hierarchy", 3,

    99

)

Now select the HierarchyGroup column --> go to Column tools --> click Sort by Column and select GroupSortOrder.

Use a hierarchical slicer with HierarchyGroup at the top and Label below.

Please make sure to sort both HierarchyGroup by the new GroupSortOrder column, and Label by SortOrder. This will ensure that the slicer displays the hierarchy groups and their fields in the correct order.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thanks and regards,

Anjan Kumar Chippa

7 Replies

v-achippa
Community Support
1 year ago
Hi jaryszek,

Thank you for reaching out to Microsoft Fabric Community.

Yes, by default power bi visuals sort the dynamic hierarchy alphabetically by the label. So we need to extract the tuple elements and explicitly sort them by the custom sort order field. Please try this below measure:

DynamicHierarchy =

SELECTCOLUMNS(

    {

        ("BillingAccountId", NAMEOF('Fct_EA_AmortizedCosts'[BillingAccountId]), 0, "Billing Hierarchy"),

        ("BillingAccountName", NAMEOF('Fct_EA_AmortizedCosts'[BillingAccountName]), 1, "Billing Hierarchy"),

        ("BillingProfileId", NAMEOF('Fct_EA_AmortizedCosts'[BillingProfileId]), 2, "Billing Hierarchy"),

        ("BillingProfileName", NAMEOF('Fct_EA_AmortizedCosts'[BillingProfileName]), 3, "Billing Hierarchy"),

        ("InvoiceSectionId", NAMEOF('Fct_EA_AmortizedCosts'[InvoiceSectionId]), 4, "Billing Hierarchy"),

        ("InvoiceSectionName", NAMEOF('Fct_EA_AmortizedCosts'[InvoiceSectionName]), 5, "Billing Hierarchy"),

        ("SubscriptionId", NAMEOF('Fct_EA_AmortizedCosts'[SubscriptionId]), 6, "Subscription Hierarchy"),

        ("SubscriptionName", NAMEOF('Fct_EA_AmortizedCosts'[SubscriptionName]), 7, "Subscription Hierarchy"),

        ("ResourceGroup", NAMEOF('Fct_EA_AmortizedCosts'[ResourceGroup]), 8, "Subscription Hierarchy"),

        ("ResourceName", NAMEOF('Fct_EA_AmortizedCosts'[ResourceName]), 9, "Subscription Hierarchy"),

        ("ResourceType", NAMEOF('Fct_EA_AmortizedCosts'[ResourceType]), 10, "ResourceType Hierarchy"),

        ("MeterId", NAMEOF('Fct_EA_AmortizedCosts'[MeterId]), 11, "ResourceType Hierarchy"),

        ("MeterCategory", NAMEOF('Fct_EA_AmortizedCosts'[MeterCategory]), 12, "ResourceType Hierarchy"),

        ("MeterSubCategory", NAMEOF('Fct_EA_AmortizedCosts'[MeterSubCategory]), 13, "ResourceType Hierarchy")

    },

    "Label", [Value1],

    "ColumnName", [Value2],

    "SortOrder", [Value3],

    "HierarchyGroup", [Value4]

)

Now in the table visual sort by the SortOrder column not Label. This make sure the hierarchy displays exactly in the custom order specified in the DAX.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thanks and regards,

Anjan Kumar Chippa

Nithinr

Resolver III

1 year ago

You're defining a dynamic hierarchy in DAX as a calculated table, but you're seeing it sorted alphabetically because Power BI automatically sorts by the first column unless explicitly overridden.

DynamicHierarchyTable =
DATATABLE(
    "Label", STRING,
    "ColumnName", STRING,
    "Index", INTEGER,
    "HierarchyGroup", STRING,
    {
        {"BillingAccountId", "Fct_EA_AmortizedCosts[BillingAccountId]", 0, "Billing Hierarchy"},
        {"BillingAccountName", "Fct_EA_AmortizedCosts[BillingAccountName]", 1, "Billing Hierarchy"},
        {"BillingProfileId", "Fct_EA_AmortizedCosts[BillingProfileId]", 2, "Billing Hierarchy"},
        {"BillingProfileName", "Fct_EA_AmortizedCosts[BillingProfileName]", 3, "Billing Hierarchy"},
        {"InvoiceSectionId", "Fct_EA_AmortizedCosts[InvoiceSectionId]", 4, "Billing Hierarchy"},
        {"InvoiceSectionName", "Fct_EA_AmortizedCosts[InvoiceSectionName]", 5, "Billing Hierarchy"},
        {"SubscriptionId", "Fct_EA_AmortizedCosts[SubscriptionId]", 6, "Subscription Hierarchy"},
        {"SubscriptionName", "Fct_EA_AmortizedCosts[SubscriptionName]", 7, "Subscription Hierarchy"},
        {"ResourceGroup", "Fct_EA_AmortizedCosts[ResourceGroup]", 8, "Subscription Hierarchy"},
        {"ResourceName", "Fct_EA_AmortizedCosts[ResourceName]", 9, "Subscription Hierarchy"},
        {"ResourceType", "Fct_EA_AmortizedCosts[ResourceType]", 10, "ResourceType Hierarchy"},
        {"MeterId", "Fct_EA_AmortizedCosts[MeterId]", 11, "ResourceType Hierarchy"},
        {"MeterCategory", "Fct_EA_AmortizedCosts[MeterCategory]", 12, "ResourceType Hierarchy"},
        {"MeterSubCategory", "Fct_EA_AmortizedCosts[MeterSubCategory]", 13, "ResourceType Hierarchy"}
    }
)

In Power BI Desktop:

Go to the Data view.

Select the DynamicHierarchyTable.

Click on the Label column.

On the Modeling tab → click Sort by Column → select Index.

jaryszek
Super User
1 year ago
Thanks for answers.

This is a result which i am getting from my code:

and your dax it is not working:
It is not groupimng hierarchies in slicer.

did you check the youtube attached?

Best,
Jacek
- v-achippa
  Community Support
  1 year ago
  Hi jaryszek,
  
  Please follow below steps:
  
  Create a calculated table like below:
  
  DynamicHierarchyTable =
  
  DATATABLE(
  
      "HierarchyGroup", STRING,
  
      "Label", STRING,
  
      "ColumnName", STRING,
  
      "SortOrder", INTEGER,
  
      {
  
          {"Billing Hierarchy", "BillingAccountId", "Fct_EA_AmortizedCosts[BillingAccountId]", 0},
  
          {"Billing Hierarchy", "BillingAccountName", "Fct_EA_AmortizedCosts[BillingAccountName]", 1},
  
          {"Billing Hierarchy", "BillingProfileId", "Fct_EA_AmortizedCosts[BillingProfileId]", 2},
  
          {"Billing Hierarchy", "BillingProfileName", "Fct_EA_AmortizedCosts[BillingProfileName]", 3},
  
          {"Billing Hierarchy", "InvoiceSectionId", "Fct_EA_AmortizedCosts[InvoiceSectionId]", 4},
  
          {"Billing Hierarchy", "InvoiceSectionName", "Fct_EA_AmortizedCosts[InvoiceSectionName]", 5},
  
          {"Subscription Hierarchy", "SubscriptionId", "Fct_EA_AmortizedCosts[SubscriptionId]", 6},
  
          {"Subscription Hierarchy", "SubscriptionName", "Fct_EA_AmortizedCosts[SubscriptionName]", 7},
  
          {"Subscription Hierarchy", "ResourceGroup", "Fct_EA_AmortizedCosts[ResourceGroup]", 8},
  
          {"Subscription Hierarchy", "ResourceName", "Fct_EA_AmortizedCosts[ResourceName]", 9},
  
          {"ResourceType Hierarchy", "ResourceType", "Fct_EA_AmortizedCosts[ResourceType]", 10},
  
          {"ResourceType Hierarchy", "MeterId", "Fct_EA_AmortizedCosts[MeterId]", 11},
  
          {"ResourceType Hierarchy", "MeterCategory", "Fct_EA_AmortizedCosts[MeterCategory]", 12},
  
          {"ResourceType Hierarchy", "MeterSubCategory", "Fct_EA_AmortizedCosts[MeterSubCategory]", 13}
  
      }
  
  )
  
  Go to Data view select the Label column, go to Column tools tab --> Sort by Column, select the required column.
  
  In the slicer, use a hierarchical slicer with HierarchyGroup and then Label below.
  
  This will group the hierarchies in the slicer and able to dynamically select any hierarchy.
  
  If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
  
  Thanks and regards,
  
  Anjan Kumar Chippa
  - jaryszek
    Super User
    1 year ago
    Ok,
    
    so I did :
    
    and now make the slicer and still ResourceType is before Subscription.
    It is not working:
    
    Best,
    Jacek
jaryszek
Super User
1 year ago
Anyone?

Best,
Jacek

Forum Discussion

How to sort dax for dynamic hierarchies?

7 Replies

Related content

Dynamic Subtitle with Hierarchy Slicers

Dynamic Y range visual calculation in two level hierarchy

Sorting

Date hierarchy

Dynamic Hierarchy