DAX Formula: Dynamic Index on Selection of slicer in Chart

[Anonymous]

Hi All,

  I want to have a index column/measure which will calculate dynamic on selection of slicer in charts. I want it to be reset to 1 on any selection.

  Any suggestion..

Thanks,

Su

First 2 screens are using an index in source itself where it doesn't change in run time but the last one changes on run time using a measure but I miss ranks in between.

[v-jiascu-msft]

Hi Sud,

Can you share a sample and the expected result please?

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft,

  I have updated my post with the screenshots let me know if you need more information.

Thanks,

Sud

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

You need to specify the last parameter of RANKX i.e TIES in order to avoid skipping RANKS

https://msdn.microsoft.com/en-us/library/gg492185.aspx

=
RANKX (
    ALLSELECTED ( EnergyMeterReponses[Squad] ),
    AVERAGEX ( RELATEDTABLE ( EnergyMeterReponses ), EnergyMeterReponses[Energy] ),
    ,
    ,
    DENSE
)

[Anonymous]

I achieve this by creating a simple table that has 2 columns:

• Selection Name to display
• ID

I create a slicer, as a dropdown, that uses the "Selection Name to Display" column, with single selection on.  To the average user, they are just picking from a list of options.  Doing this, filters that table such that there is only 1 row for context.

I create a Dynamic measure with a switch statement.  This is something like

Graph Result = SWITCH(MIN('Selection Table'[ID]),
    2, [Revenue],
    3, [TEC],
    4, [Non-Wage Costs],
    5, [Revenue per FTE],
    6, [EBIT per FTE],
    7, [TEC Per FTE],
    8, [TEC as Percent Revenue],
    9, [FTE],
    10, [Turnover],
    11, [Budget Other Expenses],
    [EBIT])

Since we are using a MIN statement, that allows me to have the first record of my Selection Table to be the default showing if anything goes wrong.