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

v-achippa's avatar
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