How to sort dax for dynamic hierarchies?

[jaryszek]

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

[jaryszek]

Anyone?

Best,
Jacek

[jaryszek]

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]

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]

Ok, 

so I did :

and now make the slicer and still ResourceType is before Subscription. 
It is not working:

Best,
Jacek

[v-achippa]

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]

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.

[v-achippa]

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