How to create a Ranking column in data table based on multiple categories

[Anonymous]

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.

RANKing Group =
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

[Jihwan_Kim · Accepted solution]

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] )
)

[Anonymous]

Hi @Jihwan_Kim , This one is perfectly working. Partition by is doing the job here. Thanks you so much 🙂

[Anonymous]

Hi @Anonymous ,

Based on the description, you seem to have solved the problem.

Please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu

[rajendraongole1]

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!!

[Anonymous]

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

[rajendraongole1]

Hi @Anonymous - Can you please try the solution provided by @Jihwan_Kim 👏