Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sorting table with 3 columns

Hi, I have a table with many columns, and I want to sort the table visualization by using the column names security number, year and month, is it possible to sort the table using these 3 columns?

 

I tried the solution https://community.powerbi.com/t5/Desktop/Sorting-a-table-using-multiple-columns/m-p/447941#M207285

= Table.Sort(#"Changed Type",{{"security number", Order.Ascending}, {"year", Order.Ascending},  {"month", Order.Ascending}})

 but it doesn't work on the table visualization.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • dax's avatar
    dax
    6 years ago

    Hi Samrawit21, 

    Although you use sort to sort column, in powerbi , it will still sort by single column. Sp workaround is that could try to add a column in table like below

    Column = RANKX(sort,sort[securitynumber],,ASC)+
RANKX ( FILTER (sort, sort[securitynumber] = EARLIER (sort[securitynumber] )), sort[year],, ASC )
 + RANKX (
        FILTER (sort, sort[securitynumber] = EARLIER (sort[securitynumber] )&& sort[year] = EARLIER ( sort[year])),
      sort[month],
        ,
        ASC

    )
    - 2

    Then set id column sort by this new column, then when you add this in table, it will sort by your requirement.

    Or you also could create a measure to rank result instead of creating a column, you could try below measure

    Measure 5 = 
VAR t =
    SUMMARIZE (
        ALLSELECTED ( sort),
       sort[id],
        sort[securitynumber],sort[year],sort[month]
        
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            ISONORAFTER (
                     sort[securitynumber], SELECTEDVALUE ( sort[securitynumber] ), DESC,
                   sort[year], SELECTEDVALUE ( sort[year] ), DESC,
                    sort[month], SELECTEDVALUE ( sort[month] ), DESC
            )
        )
    )

    Best Regards,
    Zoe Zhi

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

1 Reply

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Samrawit21, 

    Although you use sort to sort column, in powerbi , it will still sort by single column. Sp workaround is that could try to add a column in table like below

    Column = RANKX(sort,sort[securitynumber],,ASC)+
RANKX ( FILTER (sort, sort[securitynumber] = EARLIER (sort[securitynumber] )), sort[year],, ASC )
 + RANKX (
        FILTER (sort, sort[securitynumber] = EARLIER (sort[securitynumber] )&& sort[year] = EARLIER ( sort[year])),
      sort[month],
        ,
        ASC

    )
    - 2

    Then set id column sort by this new column, then when you add this in table, it will sort by your requirement.

    Or you also could create a measure to rank result instead of creating a column, you could try below measure

    Measure 5 = 
VAR t =
    SUMMARIZE (
        ALLSELECTED ( sort),
       sort[id],
        sort[securitynumber],sort[year],sort[month]
        
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            ISONORAFTER (
                     sort[securitynumber], SELECTEDVALUE ( sort[securitynumber] ), DESC,
                   sort[year], SELECTEDVALUE ( sort[year] ), DESC,
                    sort[month], SELECTEDVALUE ( sort[month] ), DESC
            )
        )
    )

    Best Regards,
    Zoe Zhi

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