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

jaryszek's avatar
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

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