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
lpimentel
New Member

Ranking by two criteria

Hi people,

 

Im havin the following issue:

Im trying to rank securities by their time to zero the position (liquidity mesure) but i want to rank it by "PRAZO DE ZERAGEM" and by "CLASSE DE ATIVO".

What I mean is, i need a single column with rank starting in 1 to N-stocks, another rank starting in 1 to N-options, another starting in 1 to N-bonds etc...

I've tried using rankx but I'm not familiar with it and it has not worked.

 

zeragem.PNG

2 REPLIES 2
Anonymous
Not applicable

Here's the M script:

 

let

    // Source is your source table; here it's a manually created one
    // for demo purposes. You should replace this with your table.
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZExCoNAEEWvEra28K+uumcRi0hSCKLBmCK3j0SIzN8ZSCDle82+P9u2Di5z820d5sl12Yb5b1wSB+KKuCZuiGPyHoukyLMoWHBkKjgT3AkOBZd6LvVJ6Yb9PF2OQ37ICyoElYKCoOprirl8XaKsgcyB7IEMgiyCfHY/5IGNxEhXkEhVG6zL9Xx/LM9TP4zjn+x7TqqDritd17pudB11vf+K4mF4b/jC8MZUGFthjIWxFsZcGHu9sddre7sX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Time to Zero" = _t, #"Asset Class" = _t]),

    // Then I change the type of the two columns
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to Zero", Int64.Type}, {"Asset Class", type text}}),


    // Need to sort the rows in order to get the ranks right.
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Asset Class", Order.Ascending}, {"Time to Zero", Order.Ascending}}),

    // This is needed to calculate the rank in the line below.
    #"Buffered Table" = Table.Buffer( #"Sorted Rows" ),

    // The calculation of rank.
    #"Rank" = Table.AddColumn(
        #"Sorted Rows", "Rank",
        (_) =>
            let
                TimeToZeroTable = Table.SelectColumns(
                    Table.SelectRows(
                        #"Buffered Table",
                        (r) => r[Asset Class] = _[Asset Class] and r[Time to Zero] <= _[Time to Zero]
                    ),
                    {"Time to Zero"}
                ),
                // If you want to have ranks with gaps, remove the List.Distinct function.
                rank = List.Count( List.Distinct( TimeToZeroTable[Time to Zero] ) )
            in
                rank
    )
in
    #"Rank"

And here's the result:

 

Ranking in Power Query.PNG

Just make the obvious changes to the script and you're done.

 

Truth be told, if you remove the line that starts with #"Sorted Rows" =, you'll also get what you need. You don't have to sort the table but if the table is really big, then it might be better to sort it.

 

Best

Darek

Anonymous
Not applicable

You should do such things in Power Query, not in DAX. This is, as much as I can see, a static ranking problem, not dynamic. So, please do yourself a favour and calculate this in Power Query.

Best
Darek

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.

Top Solution Authors