Forum Discussion

jaryszek's avatar
jaryszek
Super User
1 year ago
Solved

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_AmortizedC...
Need Help
  • v-achippa's avatar
    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

Nithinr's avatar
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.