Advanced RANKX Question

[Anonymous]

Hello, mates!

I have the following question that I have no idea what can I do:

I have a table called 'Sections' with this columns: 

• ID: code of a bus line
• FROM: start place 
• TO: finish place
• DISTANCE
• WAY

And for each ID and WAY I must rank this line IDs, ordering the sections based on distance between FROM and TO.

An exemple:

SECTIONS TABLE
ID	FROM	TO	DISTANCE	WAY
1	A	B	10	going
1	A	C	25	going
1	A	D	42	going
1	B	C	15	going
1	B	D	32	going
1	C	D	17	going
2	R	S	50	going
2	R	T	70	going
2	S	T	20	going
1	D	A	42	return
1	D	B	32	return
1	D	C	17	return
1	C	A	25	return
1	C	B	15	return
1	B	A	10	return
2	T	R	70	return
2	T	S	20	return
2	S	R	50	return

My expect result:

RANKED SECTIONS
ID	SECCION	WAY	RANK
1	A	going	1
1	B	going	2
1	C	going	3
1	D	going	4
1	D	return	1
1	C	return	2
1	B	return	3
1	A	return	4
2	R	going	1
2	S	going	2
2	T	going	3
2	T	return	1
2	R	return	2
2	S	return	3

Please help me! 😞

🙂

[tex628]

Try this: 

Rank X = 
VAR Val = Table2[ID]
VAR Val2 = Table2[Way]
Return
RANKX(FILTER(Table2;Table2[ID]=Val && Table2[Way] = Val2); Table2[Distance])

[Anonymous]

I am not able to set a table[column] as a VAR

[tex628 · Accepted solution]

Try this:

Rank X 2 = 
VAR Filt = SELECTEDVALUE(Table2[Filter])
Return
RANKX ( FILTER(ALL ( Table2 );Table2[Filter]=Filt); [Dist] )

Table2[Filter] is a calculated column:

Filter = Table2[ID] & Table2[Way]

[Dist] is a measure:

Dist = SUM(Table2[Distance])

[tex628]

Ohh, you are making a measure and not a calculated column? 

Un momento!