topic Re: How to create a Ranking column in data table based on multiple categories in DAX Commands and Tips

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

Anonymous — Sat, 22 Jun 2024 02:02:05 GMT

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

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

rajendraongole1 — Sat, 22 Jun 2024 04:32:51 GMT

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

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

Anonymous — Sat, 22 Jun 2024 06:42:51 GMT

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

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

Jihwan_Kim — Sat, 22 Jun 2024 08:34:40 GMT

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

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

rajendraongole1 — Sat, 22 Jun 2024 10:26:04 GMT

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

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

Anonymous — Mon, 24 Jun 2024 11:54:43 GMT

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

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

Anonymous — Wed, 26 Jun 2024 08:18:51 GMT

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