Multi column sort with Field parameter .

[shalmali30]

I want to sort 2 columns on select of slicer value which is filed parameter.

I have field parameter as below : 

Geo = {

    ("Region", NAMEOF('reportmain'[Region]), 0),

    ("SubRegion", NAMEOF('reportmain'[SubRegion]), 1),

    ("Unit", NAMEOF('reportmain'[Unit]), 2),

    ("Country", NAMEOF('reportmain'[Country]), 3)

}

I have a table visual with values : Geo (whatever is selected ), FY, Sum(Inventory). If I select Region from Slicer: The first column will be Region, FY,Sum(Inventory). I want to sort First column ASC and then FY desc. The sample data : 

Region	FY	Inventory
CIS	2024	23
CIS	2023	34
EU	2024	43
EU	2023	21

If I select sub region : 

SubRegion	FY	Inventory
ABC	2024	12
ABC	2023	22
QWE	2024	2
QWE	2023	2
Z	2024	2

like wise for Unit and Country. I have created a column : 

SortColumnMeasure =

VAR SelectedGeo = SELECTEDVALUE(Geo[Geo Fields])  

RETURN

    SWITCH (

        TRUE(),

        SelectedGeo = "Region",

            Reportmain[Region] & "-" & FORMAT(Reportmain[year], "0000"),

        SelectedGeo = "Subregion",

            Reportmain[Region] & Reportmain[Subregion] & "-" & FORMAT(Reportmain[year], "0000"),

        SelectedGeo = "Unit",

            Reportmain[Region] & Reportmain[Subregion] & Reportmain[Unit] & "-" & FORMAT(Reportmain[year], "0000"),

        SelectedGeo = "Country",

            Reportmain[Region] & Reportmain[Subregion] & Reportmain[Unit] & Reportmain[Country] & "-" & FORMAT(Reportmain[year], "0000"),

        -- Default case

        reportmain[Region] & "-" & FORMAT(Reportmain[year], "0000") ) 

But above switch always takes the Default case. PLease suggest 

[Anonymous · Accepted solution]

Hi @shalmali30 ,

At the moment, there are no direct features to do this. I can provide a workaround, here are the steps:

Create a measure to generate the custom sort numbers.

Sort =
VAR _sel =
    SELECTEDVALUE ( Geo[Geo Fields] )
RETURN
    RANKX (
        ALLSELECTED ( 'Table' ),
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE (
                SWITCH (
                    _sel,
                    "'Table'[Region]", MAX ( 'Table'[Region] ),
                    "'Table'[SubRegion]", MAX ( 'Table'[SubRegion] ),
                    "'Table'[Unit]", MAX ( 'Table'[Unit] ),
                    "'Table'[Country]", MAX ( 'Table'[Country] )
                )
            ),
            ,
            ASC,
            DENSE
        ) * 100
            + RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE ( MAX ( 'Table'[FY] ) ),
                ,
                DESC,
                DENSE
            ),
        ,
        ASC,
        DENSE
    )

Put it in to the visual, when you select Subregion, the visual will be sorted in ascending order by region column, and then in descending order of FY.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shalmali30]

@dharmendars007 ,I have done this already (added Sortcolumnmeasure in the visual and sort by  this column). This works perfectly fine if Region is selected as Region is in default case. If i select SubRegion from slicer, It still sorts by Region though the first column changes to SubRegion. so based on slicer selection, the sorting should be applied asc to first column and desc to FY (year). Can you please suggest over this ?

[dharmendars007]

Hello @shalmali30 , 

You can create a dynamic sort column based on the slicer selection. Since you're already using a field parameter for "Geo," you can extend the logic to control sorting behavior.

After creating this calculated measure (Sortcolumnmeasure), you need to set it as the sort column for your table.

Go to the visual where you want to apply the sort. Select the first column (like Region or SubRegion based on the slicer), and in the top menu (or in the ellipsis in the field pane), choose Sort by Column and select your calculated sort column (Sortcolumnmeasure)

SortColumnMeasure =
VAR SelectedGeo = SELECTEDVALUE(Geo[Geo Fields])
RETURN
SWITCH (
TRUE(),
SelectedGeo = "Region", Reportmain[Region] & "-" & FORMAT(Reportmain[Year], "0000") & "-" & FORMAT(Reportmain[Inventory], "000000"),
SelectedGeo = "SubRegion", Reportmain[SubRegion] & "-" & FORMAT(Reportmain[Year], "0000") & "-" & FORMAT(Reportmain[Inventory], "000000"),
SelectedGeo = "Unit", Reportmain[Unit] & "-" & FORMAT(Reportmain[Year], "0000") & "-" & FORMAT(Reportmain[Inventory], "000000"),
SelectedGeo = "Country", Reportmain[Country] & "-" & FORMAT(Reportmain[Year], "0000") & "-" & FORMAT(Reportmain[Inventory], "000000"),
Reportmain[Region] & "-" & FORMAT(Reportmain[Year], "0000") -- Default case, sorts by Region and Year)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[shalmali30]

@dharmendars007 , Thanks. I have added that measure in the sort by. It works correct if Region is selected from the slicer. If I select Subregion from slicer, though the first column changes to subregion,  the data is sorted with the Region logic only. I want it to be sorted by subregion asc first and then FY year  Desc if Subregion is selected . (Sorting should happen based on the value selected from slicer)