Rank Dates

[AvPowerBI]

Hi,

I want to do a Rank function which is based on the columns TableName, Group and DateTime

So for the latest DateTime for each TableName that is in the Group I want that to be ranked as 1

e.g

TableName = Sales

Group =  AreaA

DateTime = 03/04/2022 22:00:03

Rank = 1

Current Results

New Results with Ranks Column

Thanks

[SpartaBI · Accepted solution]

@AvPowerBI 

Rank = 
VAR _name = 'Table'[TableName]
VAR _group = 'Table'[Group]
VAR _result = 
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[TableName] = _name
         && 'Table'[Group] = _group
    ),
    'Table'[DateTime],
    ,
    DESC
)
RETURN
    _result

[tackytechtom · Accepted solution]

Hi @AvPowerBI ,

Here a solution:

And here the DAX:

TomsRankColumn = 
RANKX ( 
    FILTER ( 
        'Table', 
        'Table'[TableName] = EARLIER ( 'Table'[TableName] ) &&
        'Table'[Group] = EARLIER ( 'Table'[Group] )
    ),
    'Table'[DateTime],
    , DESC
    , DENSE
)

Let me know if this works for you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[SpartaBI · Accepted solution]

@AvPowerBI 

Rank = 
VAR _name = 'Table'[TableName]
VAR _group = 'Table'[Group]
VAR _result = 
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[TableName] = _name
         && 'Table'[Group] = _group
    ),
    'Table'[DateTime],
    ,
    DESC
)
RETURN
    _result