RANKX with multiple FILTER

[Divous]

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 adjust it to the desired output.

id	time_of_message	who	my wrong RANKX output	desired output
006jIhMDiPOZ	20.12.2021 10:00:00	robot	1
006jIhMDiPOZ	21.12.2021 10:00:00	robot	2
006jIhMDiPOZ	22.12.2021 10:00:00	support	3	1
006jIhMDiPOZ	23.12.2021 10:00:00	user	4	1
006jIhMDiPOZ	24.12.2021 10:00:00	support	5	2
006jIhMDiPOZ	25.12.2021 10:00:00	user	6	2
006jIhMDiPOZ	26.12.2021 10:00:00	support	7	3
006jIhMDiPOZ	27.12.2021 10:00:00	support	8	3
006jIhMDiPOZ	28.12.2021 10:00:00	user	9	3
006jIhMDiPOZ	29.12.2021 10:00:00	support	10	4
jj0Epbxi2L7U	23.12.2021 11:00:00	robot	1
jj0Epbxi2L7U	24.12.2021 11:00:00	user	2	1
jj0Epbxi2L7U	25.12.2021 11:00:00	support	3	1
jj0Epbxi2L7U	26.12.2021 11:00:00	support	4	1
jj0Epbxi2L7U	27.12.2021 11:00:00	support	5	1
jj0Epbxi2L7U	28.12.2021 11:00:00	user	6	2
zoYPJ957fR	25.12.2021 12:00:00	user	1	1
zoYPJ957fR	26.12.2021 12:00:00	support	2	1
zoYPJ957fR	27.12.2021 12:00:00	support	3	1
zoYPJ957fR	28.12.2021 12:00:00	user	4	2
zoYPJ957fR	29.12.2021 12:00:00	support	5	2
zoYPJ957fR	30.12.2021 12:00:00	support	6	2
zoYPJ957fR	31.12.2021 12:00:00	user	7	3
zoYPJ957fR	01.01.2022 12:00:00	support	8	3
zoYPJ957fR	02.01.2022 12:00:00	user	9	4

My simple RANKX

order = 
RANKX(FILTER('MyTable','MyTable'[id] = EARLIER('MyTable'[id])),'MyTable'[time_of_message],,ASC)

I try to add to FILTER something like, but without success.

 && 'MyTable'[who]="support"

The goal is then just to count number of all 1, 2, 3,.. per support and user for all ids and put result to visualization table. Bonus will be to count time between 1-2,2-3,3-4. But thats awaits me. I need to start with this I think 🙂

Is there any easy way in DAX?

Thanks for your help

[v-kkf-msft]

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]

Hi @v-kkf-msft Winniz,

I am not sure.. It looks like it can work -> I repliacate your steps without any error, but after close & apply in PQ it started to load data and it is running few hours and not ending. Now it shows few hunder milions of rows, but original data have around 100k :))

so I am waiting. Maybe there is something what make duplicate data

[v-kkf-msft]

Hi @Divous ,

Not sure if this way can be calculated quickly in your data, but it should be the least consumptive method I can think of. Please create two measures:

Measure = 
var PreWho = 
    CALCULATE (
        MAX ( 'Table'[who] ),
        FILTER (
            ALL('Table'),
            'Table'[id] = MAX ( 'Table'[id] )
                && 'Table'[Index]
                    = MAX ( 'Table'[Index] ) - 1
        )
    )
return 
    COUNTROWS (
        FILTER ( 'Table', 'Table'[who] <> "robot" && PreWho <> 'Table'[who] )
    )

Measure 2 = 
SUMX ( 
    FILTER ( 
        ALLSELECTED ( 'Table' ),
        'Table'[id] = MAX ( 'Table'[id] ) 
            && 'Table'[Index] <= MAX ( 'Table'[Index] )
            && 'Table'[who] = MAX ( 'Table'[who] )
    ),
    [Measure] 
)

Best Regards,
Winniz

[Divous]

Hi @v-kkf-msft ,

sorry for late response, but covid.

This is working! Great, thanks a lot!

But.. next step what I need is to count all agregated 1,2,3,4.. by id and per user and support and now when its done by measure I am wondering how to do it.

My goal is count how many # we have and put it in some bar chart or funnel. 

May I ask you for this? Thanks

[v-kkf-msft]

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.

[v-kkf-msft]

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]

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

[CNENFRNL]

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

[Divous]

Hi @CNENFRNL 

thanks for response!

Ok, I was completly wrong.. 😕 still much to learn 🙂

When I try to use your solution I found two errors:
1) # of rank should be same until one of them respond.

2) on my date it doesnt rank support correctly (maybe because there is robot again after user?)

+ if I can ask you for some improvement? Its possible to rank it after specific message in message_text column? See picture (blue desired) maybe it will be better to understand

Thanks in advance!

Best,

Divous

[AllisonKennedy]

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

@Divous  What is the rationale for your desired output? 

Order =

VAR _crID = MyTable[id] -- Get id of current row

VAR _crWHO = MyTable[ who] -- Get who of current row

RETURN

IF( MyTable[ who] <> " robot", -- Filter to ignore robot rows,

RANKX(

FILTER(MyTable, -- Start with entire table

MyTable[id] = _crID -- Filter for rows only where id is the same as current row

&& MyTable[ who] = _crWHO -- Add filter for rows only where who is the same as current row

), MyTable[ time_of_message] --Rank by time of message

, -- ignore Value argument

, ASC --Sort Ascending

, Skip --If two tied at the same time for Rank = 1, the next will be rank 3 (not 2)

)

)

I am getting the below results with your sample data: 

[Divous]

Hi @AllisonKennedy 

first of all thanks for your brilliant response. It looks like its working without any doubt and your description.. wow - its exactly what I need to understand what is happend 🙂 Thanks for this!

My rationale of desired output is to calculate how many times user and support write to each other before solving the problem. The total number of messages is also interesting, but this "rotation" is important to me. I imagined that I would then count all 1 per user, 1 per support, 2 per user, 2 per support,.. so then I would know for example that support "returned" to the chat x times.
It is useful to know how long the reactions of one of the communicating sides takes, or then to calculate the total time, the average between the exchanges of messages, etc. This is all for chat statistics and QA.
Then I want to put it in some funnel and see the throughput of all or then filtered problems.


Could I ask you for one more improvement that I canot add to your solution?
In the original data, I also have a column with the content of the message. Can be "Order" count from the time the text appears in the chat? The column is named "message_text" and the text we are looking for is "% someone% connected.." 🙂
The point is that the user can talk to the robot for a while, but the real support will join later.

[AllisonKennedy]

@Divous  Thanks for your reply and sorry for my delay. I asked about your rationale because my formula gives slightly different answers to what you had in your desired output and I just wanted to check if you had all the right info or just human error? Is my formula still working for you? Please mark as solution if so. 

For your additional question, open a new thread and post more info on what that message_text column looks like and your desired output column again - that is really helpful for us to understand exactly what you need. 

[Divous]

Hi @AllisonKennedy ,

thanks for reaching out! Info from you is really good and working good, but answers are slighly different so I am still trying to achieve my goals.

I open new thread as you suggested and hoping that I described everything and in better way. Thread is here
Chat statistics - Microsoft Power BI Community

[parry2k]

@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!