Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Hi @Anonymous ,
This could have to do with the cross filter direction of the relationship with your 'Fiscal Years' table.
Jan
Hey @Anonymous I have many measures with the same dax to show the measure for latest fiscal year and they are working fine.
@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.
@AnonymousI guess there is something wrong with your measure.
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 Var MaxYear=Calculate(MAX('Fiscal Years'[FY],filter(all('fiscal Years')))
Your Filter function is missing an argument, even if i use this:
@AnonymousIm sorry but its not working
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |