Capture Rank position

[Anonymous]

Hi,

Report has Filter "Airport" (disconnected filter used for other reason).

Report has table with Airport and Capacity columns.

Created another calculated column _Rank (based on Capacity value).

Now I want to create another Calculated column "_NewColumn", to capture the Rank position of selected Airport.

Above screen print, 'Airport1' is selected in Filter.

As per ranking, Airport1 has _Rank = 3.

Need to create new '_NewColumn', which populate with Airport1's _Rank (3), and so on.

Please help

[Anonymous · Accepted solution]

Here's the solution in full:

Measures:

Airport Rank = 
var __oneAirportVisible = HASONEVALUE( AirportCapacities[Airport] )
return
if ( __oneAirportVisible,
    RANKX (
        ALLSELECTED( AirportCapacities ),
        CALCULATE( SUM ( AirportCapacities[Capacity] ) )
    )
)

Selected Airport Rank = 
var __selectedAirport = SELECTEDVALUE ( AirportSlicer[Airport] )
var __oneAirportInView = HASONEVALUE( AirportCapacities[Airport] )
var __visibleAirports = ALLSELECTED( AirportCapacities[Airport] )
var __visibleAirportsWithRanks =
    CALCULATETABLE(
        ADDCOLUMNS(
            __visibleAirports,
            "_Rank", [Airport Rank]
        ),
        ALL( AirportCapacities )
    )
var __selectedAirportRank =
    MAXX(
        FILTER(
            __visibleAirportsWithRanks,
            AirportCapacities[Airport] = __selectedAirport
        ),
        [_Rank]
    )
return
    if ( __oneAirportInView, __selectedAirportRank )

Best

Darek

[Anonymous]

Awesome!!!

It works for me.

Thanks a lot for greatr help.

Thanks,

[Anonymous]

Mate, you need a measure, not a calculated column. You drop the measure on your table and you've got what you wanted.

Try this measure.

[Selected Airport Rank] =
var __selectedAirport = SELECTEDVALUE ( Slicer[Airport] )
var __visibleAirports =
	SELECTCOLUMNS(
		CALCULATETABLE(
			AirportCapacities,
			ALLSELECTED (
				AirportCapacities[Airport]
			)
		),
		"Airport", AirportCapacities[Airport],
		"Capacity", AirportCapacities[Capacity]
	)
var __selectedAirportRank =
	CALCULATE(	
		RANKX (
			__visibleAirports,
			[Capacity]
		),
		AirportCapacities[Airport] = __selectedAirport
	)
return
	__selectedAirportRank

I have not yet tested it so it might not work. Tweaks will be needed in this case. But I'll test it and will tell you whether it works or not. If not, I'll craft the correct version (but you should try yourself as well).

Best

Darek

[Mariusz]

Hi @Anonymous 

Unfortunately Columns are calculated only on load of the model ( not dynamic ) , you could create a Measure that will display current selection in a Table or Matrix visual.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.