Forum Discussion
Rank Dax help
hi everyone
Need your help to get Rank Dax .
Below table is the requirement.
rank dax should be based on name and solutions for FY basis.
for ex:- A name with abc solution in FY23 should be rank 1
also Name is from Name table ,solutions is from solution table and FY is from time table .
any help will be appreciated 🙂
| name | solutions | FY | rank |
| A | abc | fy23 | 1 |
| A | abc | fy22 | 2 |
| A | abc | fy21 | 3 |
| A | def | fy21 | 1 |
| B | abc | fy23 | 1 |
| B | abc | fy22 | 2 |
| B | abc | fy21 | 3 |
Hi riishabhzz ,
I noticed that you have three tables and your fields in your table visual are from different tables.
And because of the model relationships, when you put the right rank meausre into it, fields will be changed.
It's a little hard to explain, but that's the way it is.
If you want to keep the expected output when you post, try to make your Name and FY from the Solutions table.
And you'll get the right output.
Rank = RANKX(FILTER(ALLSELECTED(Solution),[Solutions]=MAX('Solution'[Solutions])&&[Name]=MAX('Name'[Name])),CALCULATE(MAX('Time'[FY])),,ASC,Dense)Rank2 = RANKX(FILTER(ALLSELECTED(Solution),[Name]=MAX('Solution'[Name])&&[Solutions]=MAX('Solution'[Solutions])),CALCULATE(MAX('Solution'[FY])),,ASC,Dense)Both measures can return the right values.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi riishabhzz ,
Here a solution in DAX for a calculated column:
Here the DAX code:
Column = RANKX ( FILTER ( 'Table', 'Table'[solutions] = EARLIER ( 'Table'[solutions] ) ), 'Table'[FY], , DESC , DENSE )I used the DAX ranking code example from here.
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi tackytechtom ,
thanks for your help , but this solution is not working .
i am getting below error :-
i found out that earlier is EARLIER and EARLIEST are usually used in calculated column.
and we can't make caluclated column as it's a live connection so only dax is the solution .
here is the dax :-dax = RANKX (FILTER (Solution,Solution[Solution Name]= EARLIER(Solution[Solution Name])),'Solution'[Created On Fiscal Year],, DESC, DENSE)any help will be appreciated 🙂
Hi riishabhzz ,
Here a solution for a DAX measure:
Measure = RANKX( ALLSELECTED ( 'Table' ), CALCULATE ( MAX ('Table'[FY] ), MAX ('Table'[solutions] ) = 'Table'[solutions] ), , DESC , DENSE )I used the DAX ranking code example from here.
A quick clarification: Both solutions - whether you are creating a calculated column or a measure - is based on the coding language DAX 🙂 So, if you are asking for a help in DAX, one might provide you with a solution either way.
Let me know if this solved your issue!
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi riishabhzz ,
Here a solution for a DAX measure:
Measure = RANKX( ALLSELECTED ( 'Table' ), CALCULATE ( MAX ('Table'[FY] ), MAX ('Table'[solutions] ) = 'Table'[solutions] ), , DESC , DENSE )I used the DAX ranking code example from here.
A quick clarification: Both solutions - whether you are creating a calculated column or a measure - is based on the coding language DAX 🙂 So, if you are asking for a help in DAX, one might provide you with a solution either way.
Let me know if this solved your issue!
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi tackytechtom
i tried your dax but still its not working 😞
i have attached screen-snip for your refrence
if possible please teach /guide me how to do it 🙂
thank you
Hi riishabhzz ,
Could you try this?
Measure 2 = VAR _value = MAX('Table'[solutions] ) RETURN RANKX( ALLSELECTED ( 'Table' ), CALCULATE ( MAX ('Table'[FY] ), _value = 'Table'[solutions] ), , DESC , DENSE )/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi riishabhzz ,
I noticed that you have three tables and your fields in your table visual are from different tables.
And because of the model relationships, when you put the right rank meausre into it, fields will be changed.
It's a little hard to explain, but that's the way it is.
If you want to keep the expected output when you post, try to make your Name and FY from the Solutions table.
And you'll get the right output.
Rank = RANKX(FILTER(ALLSELECTED(Solution),[Solutions]=MAX('Solution'[Solutions])&&[Name]=MAX('Name'[Name])),CALCULATE(MAX('Time'[FY])),,ASC,Dense)Rank2 = RANKX(FILTER(ALLSELECTED(Solution),[Name]=MAX('Solution'[Name])&&[Solutions]=MAX('Solution'[Solutions])),CALCULATE(MAX('Solution'[FY])),,ASC,Dense)Both measures can return the right values.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
