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,

 

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'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