Forum Discussion

Divous's avatar
Divous
Icon for Helper III rankHelper III
4 years ago

RANKX with multiple FILTER

Hi community,   I would like to add the order of support and users (see desired output). I manage to number the order according to the id and time columns with RANKX, but I no longer know how to ad...
Need Help
Tutorial Requests
v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

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.

 

  • Divous's avatar
    Divous
    Icon for Helper III rankHelper III
    4 years ago

    Hi v-kkf-msft Winniz,

     

    thanks for response and advice! It looks like nice solution, but unfortunately it crash on memory 😞

     

    I have over 100k rows and its growing