topic Re: How do I use RANK() to rank date in DAX Commands and Tips

How do I use RANK() to rank date

Anonymous — Mon, 12 Aug 2024 05:06:57 GMT

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!

Re: How do I use RANK() to rank date

Jihwan_Kim — Mon, 12 Aug 2024 05:40:06 GMT

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 )

Re: How do I use RANK() to rank date

Irwan — Mon, 12 Aug 2024 05:38:09 GMT

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.

Re: How do I use RANK() to rank date

Anonymous — Mon, 12 Aug 2024 06:38:43 GMT

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

Re: How do I use RANK() to rank date

Anonymous — Mon, 12 Aug 2024 06:39:07 GMT

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

Re: How do I use RANK() to rank date

Anonymous — Mon, 12 Aug 2024 06:36:59 GMT

And there's also another error message

Re: How do I use RANK() to rank date

Irwan — Tue, 13 Aug 2024 00:31:50 GMT

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.

Re: How do I use RANK() to rank date

Anonymous — Tue, 13 Aug 2024 01:11:48 GMT

Hi, thanks for explanation. It works!

Re: How do I use RANK() to rank date

Irwan — Tue, 13 Aug 2024 01:34:07 GMT

hello Anonymous

glad to be a help.

Thank you.