Forum Discussion
How do I use RANK() to rank date
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!
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.
8 Replies
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 )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.
Irwan The error is as above. How can I fix it? Thank you!
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.
