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 ,

 

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