How do I use RANK() to rank date

[Anonymous]

Hi, 

I am new to PowerBI!

What I have is the EQP_ID & FCST_TIME, and I would like to add a column  called "Rank' to display the FCST_OUT_TIME ranking by Measure Function

The earliest FCST_OUT_TIME will be ranked as 1, 2,... and so on 

Time includes year, month, day, hour and minute

The total number of of equippment is 85 

While some equippment is not being used, the time will be null(and rank 0)

How do I solve it, thanks!

[Irwan · Accepted solution]

hello @Anonymous 

please check if this accomodate your need.

create new calculated column with following DAX:

Rank =
var _Rank = 
RANKX(
    FILTER(
        'Table',
        'Table'[FCST_OUT_TIME]
    ),
    'Table'[FCST_OUT_TIME],
    ,ASC
)
Return
IF(
    ISBLANK('Table'[FCST_OUT_TIME]),
    0,
    _Rank
)

Hope this will help you.

Thank you.

[Anonymous]

@Irwan The error is as above. How can I fix it? Thank you!

[Irwan]

hello @Anonymous 

it couldnt find the column probably because the column name that i wrote is different from your column name.

otherwise please check whether you use measure or calculated column.

@Jihwan_Kim 's solution is using measure while my solution is calculated column.

That systax error is @Jihwan_Kim 's solution.

you are free to use either of them depend on your needs but please do take a note that writing DAX in calculated column and measure is slightly different.

Hope this will help you.

Thank you.

[Anonymous]

Hi, thanks for explanation. It works! 

[Irwan]

hello @Anonymous 

glad to be a help.

Thank you.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please check the below picture and the attached pbix file.

It is for creating a measure.

RANK function (DAX) - DAX | Microsoft Learn

Rank measure: =
IF (
    HASONEVALUE ( data[eqp_id] ),
    RANK (
        SKIP,
        SUMMARIZE (
            FILTER ( ALL ( data ), data[fcst_out_time] <> BLANK () ),
            data[eqp_id],
            data[fcst_out_time]
        ),
        ORDERBY ( data[fcst_out_time], ASC )
    ) + 0
)

[Anonymous]

@Jihwan_Kim  It seems that I can't use the function RANK() on my laptop. How can I fix, thank you!

[Anonymous]

And there's also another error message