Dynamically Rank rows in PivotTable based on a measure and the current context. RRS feed

  • Question

  • Hi Guys,

    I'm basically trying to write a DAX measure that replicates the good old Excel 'Rank' function whereby you rank a given number by the column that its in.

    I have a measure [Loyalty Conversion] which represents the number of potential customers converted to out loyalty programme and I have a bunch of different reports that measure this across a number of dimensions such as stores, regions, dates etc. My inital attempt has invlved running the measure through the RANKX function:

    =RANKX(ALL('Store Attributes'[Store]),[Loyalty Conversion]) 

    The issue I have is that as things stand not only do I have to hard code the column into the measure but the thing fundamentally doesn't work if I want to only show a subset of the dimension in question (because the 'All' opens up the whole thing). I need to ensure that the measure takes into account the current context of the PivotTable.

    Any ideas?


    Thursday, August 2, 2012 12:11 AM


All replies