Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

RANKX

After trying the full day, I'm hopeless to find the answer, how to rank for one NAME and DATE over the BATCH

 

batch

(number)

date (date)name (text)rank
101/1/2022abc1
221/1/2022abc2
221/1/2022abc2
221/1/2022abc2
461/1/2022abc3
461/1/2022abc3
585/1/2022abc1
585/1/2022abc1
675/1/2022abc2

 

trying all combination of  RANX, FILTER,ALLSELECTED... it returns '1' value only

please help

 

thanks a lot!

1 ACCEPTED SOLUTION
Shaurya
Memorable Member
Memorable Member

Hi @Anonymous,

 

Since you want to rank the Batch column for a particular Name and Date, you'll have to consider all the other records in the Name and Date column with that value. Therefore, you'll have to use the EARLIER() function along with RANKX() and FILTER().

 

Use this DAX Formula:

 

Rank = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Name]=EARLIER('Table'[Name])),'Table'[Batch],,ASC,DENSE)

 

Here's the result:

 

Screenshot 2022-10-06 060902.jpg 

 

Works for you? Mark this post as a solution if it does!

View solution in original post

3 REPLIES 3
rob7one
Helper I
Helper I

What if i would like to do the rank for one coulmn more? For example to create the rank for the batch column for month, name and additional location (paris, berlin, rome, new york, hongkong etc.). Is this possible?

Shaurya
Memorable Member
Memorable Member

Hi @Anonymous,

 

Since you want to rank the Batch column for a particular Name and Date, you'll have to consider all the other records in the Name and Date column with that value. Therefore, you'll have to use the EARLIER() function along with RANKX() and FILTER().

 

Use this DAX Formula:

 

Rank = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Name]=EARLIER('Table'[Name])),'Table'[Batch],,ASC,DENSE)

 

Here's the result:

 

Screenshot 2022-10-06 060902.jpg 

 

Works for you? Mark this post as a solution if it does!

Anonymous
Not applicable

Measure 4 = RANKX(ALL('DATE', 'NAME'), calculate(MAX(NUMBER)))
...this is one of my thousends versions which work but return only '1' value

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.