topic Re: rankx measure based on points and name in DAX Commands and Tips

rankx measure based on points and name

Krijgersss — Mon, 01 Jul 2024 08:18:12 GMT

Hello i need some help i use this measure to give everyone a rank :

RANKX(

ALL(

Tour_De_France_Poule[Tourploeg]),

CALCULATE(

SUM(

Tour_De_France_Poule[Punten])

)

now is the problem that some people has the same amount of point so they wil get the same rank. i want this measure that if 2 people has the same amount of points it will look at the name and give the by name there rank so for example

5 max 10
5 arjan 10

i want the answer to be

5 Arjan 10
6 Max 10

because the A comes fore the M in the alpabet

Re: rankx measure based on points and name

bhanu_gautam — Mon, 01 Jul 2024 10:57:07 GMT

Krijgersss , To achieve this you can use name as secondry criterion

Rank =

RANKX(

ALL(Tour_De_France_Poule[Tourploeg]),

CALCULATE(

SUM(Tour_De_France_Poule[Punten])

) +

RANKX(

ALL(Tour_De_France_Poule[Tourploeg]),

Tour_De_France_Poule[Tourploeg],

ASC,

DENSE

) / 1000000,

ASC,

DENSE

)

Re: rankx measure based on points and name

Krijgersss — Mon, 01 Jul 2024 11:15:45 GMT

i get this error by your formula

Re: rankx measure based on points and name

bhanu_gautam — Mon, 01 Jul 2024 11:32:59 GMT

Can you share sample data for testing or convert this error in english translation

Re: rankx measure based on points and name

Krijgersss — Mon, 01 Jul 2024 12:02:54 GMT

A single value for column 'Tourploeg' in table 'Tour_De_France_Poule' cannot be determined. This can happen when the formula of the measure refers to a column that contains many values and no aggregation such as minimum, maximum, count, or sum has been specified to yield a single result.

in the screenshot is example of the data how it looks now

Re: rankx measure based on points and name

bhanu_gautam — Mon, 01 Jul 2024 12:29:33 GMT

Here is the updated codewith PBIX fileand it is working created a new column using

Rank =

VAR CurrentName = 'Table'[Name]

VAR CurrentPoints = 'Table'[Points]

RETURN

RANKX(

FILTER(

'Table',

'Table'[Points] = CurrentPoints

'Table'[Name],

ASC,

DENSE

) +

RANKX(

ALL('Table'),

'Table'[Points],

DESC,

DENSE

) - 1

Re: rankx measure based on points and name

Krijgersss — Mon, 01 Jul 2024 12:37:29 GMT

i have 1 error in your formula it doesnt reconize my tabel with points called "Tour_De_France_Poule[Punten]"

it is correctly spelled

Re: rankx measure based on points and name

bhanu_gautam — Mon, 01 Jul 2024 12:54:56 GMT

Krijgersss ,

My table name is ='Table'

And column contains name is ='Table'[Name]

And column containing point is ='Table'[Points]

Can you replace them with the name in your table and it will work or you can try renaming columns as per the details shared by me

Re: rankx measure based on points and name

Krijgersss — Mon, 01 Jul 2024 13:09:21 GMT

i tryed it but it gives me the same error as before

A single value for column 'Tourploeg' in table 'Tour_De_France_Poule' cannot be determined. This can happen when the formula of the measure refers to a column that contains many values and no aggregation such as minimum, maximum, count, or sum has been specified to yield a single result.

Re: rankx measure based on points and name

bhanu_gautam — Mon, 01 Jul 2024 13:25:39 GMT

You must be adding measure go to modelling tab and add new column