Sort multiple level row header in matrix

[FXFelix]

Hello,

I want to achieve the following presentation with a matrix :

(these are row headers)

I cannot find a way to sort the second level by alphabetical order 😶

I have tried this datatable :

FPX_Hierarchie =

DATATABLE (

    "CA Type"; STRING;

    "Categorie"; STRING;

    "Ordre";INTEGER;

    {

        {"CA Comparable"; "Intégrés"; 1};

        {"CA Comparable"; "Masters Franchisés"; 2};

        {"CA Total"; "Intégrés"; 3};

        {"CA Total"; "Masters Franchisés"; 4};

        {"CA Total"; "Franchisés"; 5}

    }

)

But I cannot apply the order because I have same values :

Thanks in advance for your help.

ephixe

[DataNinja777]

Hi @FXFelix ,

Power BI does not allow you to sort second-level row headers in a matrix alphabetically when the same value appears under multiple first-level groups. To resolve this, you need to create a unique composite key that combines both the first-level and second-level values, and then use a custom sort order.

First, create a DATATABLE that assigns a unique sort order to each combination of CA Type and Catégorie:

FPX_Hierarchie =
DATATABLE (
    "CA Type", STRING,
    "Categorie", STRING,
    "SortKey", INTEGER,
    {
        {"CA Comparable", "Intégrés", 1},
        {"CA Comparable", "Masters Franchisés", 2},
        {"CA Total", "Franchisés", 3},
        {"CA Total", "Intégrés", 4},
        {"CA Total", "Masters Franchisés", 5}
    }
)

Then add a calculated column that concatenates CA Type and Catégorie to make each row unique:

FPX_Hierarchie = 
ADDCOLUMNS (
    FPX_Hierarchie,
    "CompositeCategorie", [CA Type] & " - " & [Categorie]
)

In the table you are visualizing in the matrix, create the same CompositeCategorie column and establish a relationship with the FPX_Hierarchie table based on that field. Then use CompositeCategorie in your matrix row headers and sort it by SortKey. To display only the Catégorie name without the CA Type prefix while preserving the correct sort order, create a new column that retrieves only the Catégorie:

VisibleCategorie = LOOKUPVALUE(
    FPX_Hierarchie[Categorie],
    FPX_Hierarchie[CompositeCategorie],
    'YourTable'[CompositeCategorie]
)

Use VisibleCategorie in your matrix visual and sort it by the SortKey in the background. This approach ensures correct alphabetical sorting within each CA Type group while avoiding duplicate value issues.

Best regards,

[FXFelix]

Hello @DataNinja777 

Thanks for your quick answer 🙂

I'll try it next week !

Just a question : with this approach, I can keep the two levels CA Type and Categorie ?

[v-mdharahman]

Hi @FXFelix,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to find a way to sort the second level row header. As @DataNinja777 has already responded to your query and you have acknowledged his response and needs some time to go through the response, kindly update the status of the issue whenever you get the chance to go through the provided response.

I would also take a moment to thank @DataNinja777, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[FXFelix]

Well...I guess I have another difficulty 🤔

The values of the matrix are two columns like that :

CA = 
VAR __CAType = SELECTEDVALUE ( FPX_Hierarchie[CA Type]; "Non sélectionné" )
VAR __Categorie = SELECTEDVALUE ( FPX_Hierarchie[Categorie]; "Non sélectionné" )
RETURN
    IF (
        __CAType = "Non sélectionné" || __Categorie = "Non sélectionné";
        // Débogage visuel
        "Sélection manquante : " & __CAType & " / " & __Categorie;
        // Sinon, exécuter le SWITCH :
        SWITCH (
            TRUE();
            __CAType = "CA Comparable" && __Categorie = "Intégrés"; [CA_FPX_COMP_INT];
            __CAType = "CA Comparable" && __Categorie = "Masters Franchisés"; [CA_FPX_COMP_MASTERS_FRA];
            __CAType = "CA Total" && __Categorie = "Intégrés"; [CA_FPX_TOT_INT];
            __CAType = "CA Total" && __Categorie = "Masters Franchisés"; [CA_FPX_COMP_MASTERS_FRA];
            __CAType = "CA Total" && __Categorie = "Franchisés"; [CA_FPX_TOT_FRA];
            BLANK()
        )
    )

Therefore, I cannot link this with the new column CompositeCategorie...

Is there is another way to do it ?

Thanks !

[FXFelix · Accepted solution]

I've found a trick to solve my problem: I add a space after the word if I have duplicates.

This way, I can sort the labels by the sort keys.

A bit far-fetched, but in my case it works...