Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Capture Rank position

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's avatar
    Anonymous
    7 years ago

    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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Awesome!!!

       

      It works for me.

      Thanks a lot for greatr help.

       

      Thanks,

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    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.
    Mariusz Repczynski

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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