Forum Discussion

Divous's avatar
Divous
Helper 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
Community Support
4 years ago

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's avatar
    Divous
    Helper III
    4 years ago

    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's avatar
      v-kkf-msft
      Community Support
      4 years ago

      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's avatar
        Divous
        Helper III
        4 years ago

        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