Forum Discussion

rainynights's avatar
rainynights
Icon for Helper II rankHelper II
6 years ago
Solved

Using RANKX to rank dates across various combinations of unique categories

So I have an interesting use-case for something like this.

 

Let's say we have the following table that summarizes meetings with customers, that lists:

- The Name of our Sales Rep

- The customer they met

- Whether the meeting was in-person or over the phone,

- The date the meeting happened

 

 

What I'm trying to do is run a RANKX based on the date column, so it sorts from earliest to latest. However, I want this RANKX to work across unique combinations of the first 3 columns. So the expected output for instance should be:

 

 

Essentially, each unique combination of the first 3 categories is treated as a unique category for the purposes of rankx, and then it's sorted by the date. 

 

As an example, Sarah (column 1) met with Customer 3 (column 2) via in-person meetings (column 3) 3 separate times, so the rank goes up to 3 for that scenario. 

 

I'm unclear how to go about starting to create this rankx calculated column.

 

What I probably want is some variation of the following that actually works:

Rank = RANKX(

DISTINCT(SELECTCOLUMNS([Sales Rep], [Customer], [Meeting Type])),[date] ,,DESC,dense)


I just can't seem to structure the DAX quite right to achieve the intended effect!

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi rainynights ,

    Try this DAX:

    Column 3 = 
RANKX(
    FILTER(
        'Table',
        [Sales Rep] = EARLIER('Table'[Sales Rep])
        &&
        [Customer] = EARLIER('Table'[Customer])
        &&
        [Meeting Type] = EARLIER('Table'[Meeting Type])
    ),
    [Date].[Date],
    ,
    ASC,
    Dense)

     

    Best regards,
    Lionel Chen

     

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

4 Replies