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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Using RANKX with filter for max Year

Hey Guys,

I am ranking my clients based on two measures one is the client score and second is total revenue.
Since i have many clients some clients have the same client score hence the ranking based on total revenue.

Now the issue here is that i want the measure to be filtered by the maximum Financial Year.
So i wrote the below code: 

Ranking_Clients = 
Var MaxYear=MAX('Fiscal Years'[FY])
Return
IF(ISFILTERED('Fiscal Years'[FY]),
 RANKX (
    ALL ( 'Responses received' ),
    RANKX ( ALL ( 'Responses received' ),[Client Score],, DESC )
        + DIVIDE (
            RANKX ( ALL ( 'Responses received' ),[Total Revenue] ,, DESC ),
            ( COUNTROWS ( ALL ( 'Responses received' ) ) + 1 )
        )
),
CALCULATE(
     RANKX (
    ALL ( 'Responses received' ),
    RANKX ( ALL ( 'Responses received' ),[Client Score],, DESC )
        + DIVIDE (
            RANKX ( ALL ( 'Responses received' ),[Total Revenue] ,, DESC ),
            ( COUNTROWS ( ALL ( 'Responses received' ) ) + 1 )
        )
),'Fiscal Years'[FY]=MaxYear))


But its not affecting the results.

 

8 REPLIES 8
Anonymous
Not applicable

Hi @Anonymous ,

 

This could have to do with the cross filter direction of the relationship with your 'Fiscal Years' table.

 

Jan 

Anonymous
Not applicable

Hey @Anonymous I have many measures with the same dax to show the measure for latest fiscal year and they are working fine.

Anonymous
Not applicable

@Anonymous 

 

I think your max year changing to same value.

 

Can you update variable and check

var max_year=calculate(max(table[year_column]),filter(all(table[year_column]))

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

 

 

 

Anonymous
Not applicable

@AnonymousI guess there is something wrong with your measure.

Anonymous
Not applicable

use your measure only just update your first line which var max_year

 

Ranking_Clients = 
Var MaxYear=Calculate(MAX('Fiscal Years'[FY],filter(all('fiscal Years')))
Return
IF(ISFILTERED('Fiscal Years'[FY]),
 RANKX (
    ALL ( 'Responses received' ),
    RANKX ( ALL ( 'Responses received' ),[Client Score],, DESC )
        + DIVIDE (
            RANKX ( ALL ( 'Responses received' ),[Total Revenue] ,, DESC ),
            ( COUNTROWS ( ALL ( 'Responses received' ) ) + 1 )
        )
),
CALCULATE(
     RANKX (
    ALL ( 'Responses received' ),
    RANKX ( ALL ( 'Responses received' ),[Client Score],, DESC )
        + DIVIDE (
            RANKX ( ALL ( 'Responses received' ),[Total Revenue] ,, DESC ),
            ( COUNTROWS ( ALL ( 'Responses received' ) ) + 1 )
        )
),'Fiscal Years'[FY]=MaxYear))
Anonymous
Not applicable

@Anonymous Var MaxYear=Calculate(MAX('Fiscal Years'[FY],filter(all('fiscal Years')))
Your Filter function is missing an argument, even if i use this:

Var MaxYear=calculate(max('Fiscal Years'[FY]),FILTER('Fiscal Years',all('Fiscal Years'[FY])))
Its not working.And i am using my measure with your max year variable
Anonymous
Not applicable

 
Sorry @Anonymous 
update it Like this CALCULATE(MAX(Table[Skill]),(ALL(Table[Skill])))
Anonymous
Not applicable

@AnonymousIm sorry but its not working

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.