Forum Discussion
How to create a Ranking column in data table based on multiple categories
Hi,
I need to create a Ranking Column based on 2 categories, (Description_Task and Approval_Date) for each NSRNUM as displayed below. I created a calculated column using DAX. But that doesn't give the desired result. Attached one screenshot for one of the NSRNUM. Kindly help.
RANKX (
FILTER (
'Table',
'Table'[NSRNUM] = EARLIER ( 'Table'[NSRNUM] )
&& (
'Table'[DESCRIPTION_TASK] < EARLIER ( 'Table'[DESCRIPTION_TASK] )
|| 'Table'[approval_date_fmt] < EARLIER ( 'Table'[approval_date_fmt] )
)
),
'Table'[approval_date_fmt],
,
ASC,
DENSE
)
Expectation:
TIA,
Tanisha
Hi, I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
It is for creating a new column.
RANK function (DAX) - DAX | Microsoft Learn
expected result calculated column = RANK ( SKIP, data, ORDERBY ( data[approval_date], ASC ), , PARTITIONBY ( data[nsrnum], data[description_task] ), MATCHBY ( data[nsrnum], data[description_task], data[approval_date] ) )
6 Replies
Hi, I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
It is for creating a new column.
RANK function (DAX) - DAX | Microsoft Learn
expected result calculated column = RANK ( SKIP, data, ORDERBY ( data[approval_date], ASC ), , PARTITIONBY ( data[nsrnum], data[description_task] ), MATCHBY ( data[nsrnum], data[description_task], data[approval_date] ) )Hi Jihwan_Kim , This one is perfectly working. Partition by is doing the job here. Thanks you so much 🙂
Hi Anonymous - can you try the below calcuated column
Ranking_Group =
VAR CombinedValue =
'Table'[DESCRIPTION_TASK] & FORMAT('Table'[approval_date_fmt], "yyyymmdd")
RETURN
RANKX(
FILTER(
'Table',
'Table'[NSRNUM] = EARLIER('Table'[NSRNUM])
),
CombinedValue,
,
ASC,
DENSE
)Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Hi rajendraongole1 ,
Dont see any ranking with this DAX expressions. Attaching the screenshot below also my sample pbix file. Kindly have a look. Thanks.
Best Regards,
Tanisha
