RANKX with multiple FILTER

UPDATE: See attached file (below signature) and difference between 1-2, 2-3, etc calculation: 

Divous  What is the rationale for your desired output? 

I am getting the below results with your sample data: 

Your question has nothing to do with RANKX at all. Only a tricky calculation based off on different delimiters.

Hi Divous ,

First create the Index column in the Power Query Editor.

Then create the following calculated columns:

PreWho = 
CALCULATE (
    MAX ( 'Table'[who] ),
    FILTER (
        'Table',
        'Table'[id] = EARLIER ( 'Table'[id] )
            && 'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
    )
)

Column = 
IF ( 'Table'[who] = "robot", BLANK (), IF ( [PreWho] <> 'Table'[who], 1, 0 ) )

Output = 
CALCULATE (
    SUM ( 'Table'[Column] ),
    FILTER (
        'Table',
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
            && 'Table'[id] = EARLIER ( 'Table'[id] )
            && 'Table'[who] = EARLIER ( 'Table'[who] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Divous ,

If you use the M code in Power Query Editor and then create a calculated column, will you encounter memory problems this way?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndNBC4IwFAfwryI7C25v6rRbUIciSYIOJV6EIkVSNCH69Kl5Sd82GOwwGD/+7+1tSUIo9YvdI9rk8fFKbMLAAdovYBajKzosax31B1ZTZdWr3zy7siSpjUpmLAGVbVfXVTNYJoMchV17a1TK1cSBDHqqOKnyNXFcBoUpDFR1SlWoiXNHWBR0W2fvHA7iPJsCUw8ekS4q/+aHKA9V8+eCQN8UClMYqBr8vZhPdYn3oSfup1l7MJl4eSkL46NmXuCCCTMWqCqUdBVqonDGqRljqgo5YpxRgDwIRYCiKab/L+kX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, time_of_message = _t, who = _t, #"desired output" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"time_of_message", type datetime}, {"who", type text}, {"desired output", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"who"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"id"}, {{"All", each _, type table [id=nullable text, time_of_message=nullable datetime, who=nullable text, desired output=nullable number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"IndexByid",1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"id", "All"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"id", "time_of_message", "who", "desired output", "IndexByid"}, {"id", "time_of_message", "who", "desired output", "IndexByid"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"IndexByid", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [who] = "robot" then null 
else if [IndexByid] = 1 then 1
else if 
       [who] <> (try #"Added Index"{[Index]-2}[who] otherwise null) then 1 
     else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

Output = 
CALCULATE (
    SUM ( 'Table'[Custom] ),
    FILTER (
        'Table',
        'Table'[IndexByid] <= EARLIER ( 'Table'[IndexByid] )
            && 'Table'[id] = EARLIER ( 'Table'[id] )
            && 'Table'[who] = EARLIER ( 'Table'[who] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Divous ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

Divous check my solution here and that will do it for you. Solved: Re: RankX problems - Page 2 - Microsoft Power BI Community

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!