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