Need help - how to rank based on filter and group by specific column?

[uir]

Many thanks for your help in advance

raw data is like the following:

after setting the filter: purchase date between 202108-202110, how can I get the following result?

(filter data that purchase date between 202108-202110, and rank by customerID)

[smpa01]

@uir  can you try this measure?

Rank =
RANKX (
    FILTER ( ALLSELECTED ( tbl ), tbl[custID] = MAX ( tbl[custID] ) ),
    [_maxPurchaseDate],
    ,
    ASC
)

[v-zhangti]

Hi, @uir 

You can try the following methods.

Measure:

Rank = 
CALCULATE (
    COUNT ( 'Table'[CustomerID] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [CustomerID] = MAX ( 'Table'[CustomerID] )
            && [Purchase date] <= MAX ( 'Table'[Purchase date] )
    )
)

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @uir ,

creating maeasure like this, requires a data model instead of a single table. For this reason i created two additional tables:

• Customer and
• Date

I created relationships between the new tables and the existing one (the table that contains the sample data). I called the existing table: fact. The screenshot below shows the data model:

Then I created the measure below:

Measure = 
var outerGroupElement = MAX( 'Customer'[CustomerID] )
var innerGroupTable = CALCULATETABLE( VALUES( 'Fact'[Purchase Date] ) , ALLSELECTED( 'Date'[Date] ) )
var __t =  
        GENERATE(
            VALUES( 'Customer'[CustomerID] )
            , innerGroupTable
        )
var __t1 =
    ADDCOLUMNS(
        __t
        , "rk" 
            , rankx( __t
                , CALCULATE( MAX('Fact'[Purchase Date] ) 
                    , ALL( 'Date' ) 
                ) 
                , 
                , ASC 
            )
    )
return
IF( MAX( 'Date'[Date] ) in innerGroupTable
    , var cid = MAX( 'Customer'[CustomerID] )
    var pd = MAX( 'Date'[Date] )
    return
    MAXX( FILTER( __t1 , [CustomerID] = cid && [Purchase Date] = pd) , [rk] )
    , BLANK()
)

This allows to create a simple report like below:

Be aware that the visuals (slicer, table) are using columns from the new tables instead of the columns from the old table.

Hopefully, this provides some new ideas on how to tackle your challenge.

Regards,

Tom

[amitchandak]

@uir , You can use filter on purchase date

or create this into date and use a filter on connected date table

date = date(left([purchase Date],4), right([purchase date],2) ,1) 

You can not get that with Rank,

So create a measure

countx(filter(allselected(Table), Table[Customer] =  max(Table[Customer ID])   && Table[Purchase Date] <= Max(Table[Purchase Date]) ) )

[uir]

countx need 2 parameters, however, it seems that your measure only have 1, could u plz share the 2nd parameter?

[amitchandak · Accepted solution]

@uir , My bad,

You needed coutrows

countrows(filter(allselected(Table), Table[Customer] = max(Table[Customer ID]) && Table[Purchase Date] <= Max(Table[Purchase Date]) ) )