Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amalrio
Helper V
Helper V

sort a mettrix by year and row labels

Hi Gurus, 

 

I have a below metris which I want to share in a way that names of the latest(2021) year besome the first  row of the matrix and name if the last year (2025) become the last row of the matrix

 

for instance SGE S18, Caves Creek and SGE S18, Lens AB should be the first row of the matrix, GBO Expansion should be second row of the matrix, Caves Creeks Floodplain should be the third row of the metrix and so on.. when I sort by facility it does not give me the desire result, it must be something of a custome sort to achieve this (what I think anayway)

 

is there any way I can achieve this.

 

amalrio_1-1605491255927.png

 

 

 

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

Hi @amalrio ,

 

Based on your description, you can create a rank column as follows and sort by it.

 

Rank =

if(

    [2021]=BLANK(),

    IF(

        [2022]=BLANK(),

        IF(

            [2023]=BLANK(),

            IF(

                [2024]=BLANK(),

                "5",

                "4"

            ),

            "3"

        ),

        "2"

    ),

    "1"

)

 

Result:

v-yuaj-msft_0-1605661695217.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

View solution in original post

3 REPLIES 3
v-yuaj-msft
Community Support
Community Support

Hi @amalrio ,

 

Based on your description, you can create a rank column as follows and sort by it.

 

Rank =

if(

    [2021]=BLANK(),

    IF(

        [2022]=BLANK(),

        IF(

            [2023]=BLANK(),

            IF(

                [2024]=BLANK(),

                "5",

                "4"

            ),

            "3"

        ),

        "2"

    ),

    "1"

)

 

Result:

v-yuaj-msft_0-1605661695217.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

amitchandak
Super User
Super User

@amalrio , You have to create a new sort column based on the order you want

 

new column

Facility 1 = [Facility 1]

Facility sort = Switch( True(),
[Facility] = "SGE S18, Caves Creek",1 ,
[Facility] = "SGE S18, Lens AB ",2 ,
// add others
,6 //default value
)

 

Mark Facility sort as the sort column for Facility

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

Sortnewribbion.png

 

DavisBI
Solution Specialist
Solution Specialist

Hi, @amalrio 

 

You can click on the column heading again so that the row labels can be sorted in descending order.

If you need customized sorting, you can add an index column to the data source as a sort sequence. For example, SGE S18, Caves Creek and SGE S18, Lens AB corresponds to a value of 1, and then you can use "sort by column" to achieve this.

DavisBI_0-1605492423944.png

 

 

Mark this post as solution if this helps, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.