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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
afleiderman1
Helper I
Helper I

How to sort based on multiple columns

I am wondering how would do the following 

I have the followign table

 

SERIAL     Company         Product              Amount 

ABC            XYZ                 WATER               91

FAK             C2K                 FIRE                    21

ABC             XYZ                 ICE                15

K2A              ARKK                    EARTH              21

west              arkk                  ice                      12

DDD         ALP                    earth                 120

I would like to sort this first by the amount and then by the serial number

to have something like

 

WEST ARKK      ICE          12

FAK     C2K     FIRE          21

ABC      XYZ     WATER              91

ABC      XYZ        ICE          15

DDD    ALP         EARTH    105

 

Basically I want to sort by both the amoutn and the serial number. 

I am using the data visual table in power bi

 

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

Hi @afleiderman1 ,

I have created a simple sample, please refer to it to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRioiMApLhjiGuQUDa0lApVidayc3RG8hxNgKRbp5BrkDKCCKDrMnTGSRhaAqW8DZyBHIcg7xBelwdg0I8EJrKU4tLgLzEouxshDYjsJSLiwtIm08Aki5DIwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [serial = _t, COMPANY = _t, PRODUCT = _t, AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"serial", type text}, {"COMPANY", type text}, {"PRODUCT", type text}, {"AMOUNT", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "serial", "serial - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "serial - Copy", Splitter.SplitTextByRepeatedLengths(1), {"serial - Copy.1", "serial - Copy.2", "serial - Copy.3", "serial - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"serial - Copy.1", type text}, {"serial - Copy.2", type text}, {"serial - Copy.3", type text}, {"serial - Copy.4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"serial - Copy.2", "serial - Copy.3", "serial - Copy.4"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [#"serial - Copy.1"] = "A" then 26 else if [#"serial - Copy.1"] = "B" then 25 else if [#"serial - Copy.1"] = "C" then 24 else if [#"serial - Copy.1"] = "D" then 23 else if [#"serial - Copy.1"] = "E" then 22 else if [#"serial - Copy.1"] = "F" then 21 else if [#"serial - Copy.1"] = "G" then 20 else if [#"serial - Copy.1"] = "H" then 19 else if [#"serial - Copy.1"] = "I" then 18 else if [#"serial - Copy.1"] = "J" then 17 else if [#"serial - Copy.1"] = "K" then 16 else if [#"serial - Copy.1"] = "L" then 15 else if [#"serial - Copy.1"] = "M" then 14 else if [#"serial - Copy.1"] = "N" then 13 else if [#"serial - Copy.1"] = "O" then 12 else if [#"serial - Copy.1"] = "P" then 11 else if [#"serial - Copy.1"] = "Q" then 10 else if [#"serial - Copy.1"] = "R" then 9 else if [#"serial - Copy.1"] = "S" then 8 else if [#"serial - Copy.1"] = "T" then 7 else if [#"serial - Copy.1"] = "U" then 6 else if [#"serial - Copy.1"] = "V" then 5 else if [#"serial - Copy.1"] = "W" then 4 else if [#"serial - Copy.1"] = "X" then 3 else if [#"serial - Copy.1"] = "Y" then 2 else if [#"serial - Copy.1"] = "Z" then 1 else 0)
in
    #"Added Conditional Column"

Then create measures.

Measure =MAX('Table'[AMOUNT])*100+MAX('Table'[Custom])
rankx = RANKX(ALL('Table'),[Measure],,ASC,Dense)

vpollymsft_0-1670398713221.png

If I have misunderstood your meaning, Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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-rongtiep-msft
Community Support
Community Support

Hi @afleiderman1 ,

I have created a simple sample, please refer to it to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRioiMApLhjiGuQUDa0lApVidayc3RG8hxNgKRbp5BrkDKCCKDrMnTGSRhaAqW8DZyBHIcg7xBelwdg0I8EJrKU4tLgLzEouxshDYjsJSLiwtIm08Aki5DIwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [serial = _t, COMPANY = _t, PRODUCT = _t, AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"serial", type text}, {"COMPANY", type text}, {"PRODUCT", type text}, {"AMOUNT", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "serial", "serial - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "serial - Copy", Splitter.SplitTextByRepeatedLengths(1), {"serial - Copy.1", "serial - Copy.2", "serial - Copy.3", "serial - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"serial - Copy.1", type text}, {"serial - Copy.2", type text}, {"serial - Copy.3", type text}, {"serial - Copy.4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"serial - Copy.2", "serial - Copy.3", "serial - Copy.4"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [#"serial - Copy.1"] = "A" then 26 else if [#"serial - Copy.1"] = "B" then 25 else if [#"serial - Copy.1"] = "C" then 24 else if [#"serial - Copy.1"] = "D" then 23 else if [#"serial - Copy.1"] = "E" then 22 else if [#"serial - Copy.1"] = "F" then 21 else if [#"serial - Copy.1"] = "G" then 20 else if [#"serial - Copy.1"] = "H" then 19 else if [#"serial - Copy.1"] = "I" then 18 else if [#"serial - Copy.1"] = "J" then 17 else if [#"serial - Copy.1"] = "K" then 16 else if [#"serial - Copy.1"] = "L" then 15 else if [#"serial - Copy.1"] = "M" then 14 else if [#"serial - Copy.1"] = "N" then 13 else if [#"serial - Copy.1"] = "O" then 12 else if [#"serial - Copy.1"] = "P" then 11 else if [#"serial - Copy.1"] = "Q" then 10 else if [#"serial - Copy.1"] = "R" then 9 else if [#"serial - Copy.1"] = "S" then 8 else if [#"serial - Copy.1"] = "T" then 7 else if [#"serial - Copy.1"] = "U" then 6 else if [#"serial - Copy.1"] = "V" then 5 else if [#"serial - Copy.1"] = "W" then 4 else if [#"serial - Copy.1"] = "X" then 3 else if [#"serial - Copy.1"] = "Y" then 2 else if [#"serial - Copy.1"] = "Z" then 1 else 0)
in
    #"Added Conditional Column"

Then create measures.

Measure =MAX('Table'[AMOUNT])*100+MAX('Table'[Custom])
rankx = RANKX(ALL('Table'),[Measure],,ASC,Dense)

vpollymsft_0-1670398713221.png

If I have misunderstood your meaning, Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

 

vicky_
Super User
Super User

Yeah I tried the shift key and multiple column but I cannot get it too look the way I want

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.