Rank Dax help

[riishabhzz]

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

[v-stephen-msft · Accepted solution]

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.                      

[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/

[riishabhzz]

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 

[tackytechtom]

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/

[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/

[tackytechtom]

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/

[riishabhzz]

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 🙂