PowerPivot DAX - Dynamic Ranking Per Group (Min Per Group) -

i searching method utilize within microsoft powerpivot 2010 allow me perform dynamic ranking automatically update associated rank value based on filters , slicer values applied.

thusfar, examples have seen utilize calculate() dax function overrides existing filters within powerpivot table via all() function causes predefined filters users may apply disregarded.

to illustrate requirements, please reference example below:

(source data within powerpivot window:)

------------------------------------------------------------------------------------- claim number | claimant number | transaction date |            dollar amount ------------------------------------------------------------------------------------ abcd123456            4                1/1/2012                   $145.23 abcd123456            4                8/1/2012                   $205.12 abcd123456            4                9/1/2012                   $390.74 vdsf123455            2                3/5/2012                   $10.12 vdsf123455            2                9/5/2012                   $50.12            vdsf123455            2                12/9/2012                  $210.45 ------------------------------------------------------------------------------------------ 

now, have capability of displaying dollar amount each claim number , claimant number based on first transaction date. desire show dollars tied first transaction tied each claim# , claimant number group.

my thought process create dynamic rank each [claim number + claimant number] group similiar "partition - row_number()" function. in way, each [claim number + claimant number] group containing rank value of 1 represent first transaction tied value.

however, need rank value change if user applies filter against data within resulting pivottable.

this can illustrated below based on sample data defined above:

---------------------------------------------------------------------------------------------- claim number | claimant number | transaction date |  dollar amount |  rank | ---------------------------------------------------------------------------------------------  abcd123456             4                1/1/2012         $145.23        1  abcd123456             4                8/1/2012         $205.12        2  abcd123456             4                9/1/2012         $390.74        3  vdsf123455             2                3/5/2012         $10.12         1  vdsf123455             2                9/5/2012         $50.12         2    vdsf123455             2               12/9/2012         $210.45        3  ------------------------------------------------------------------------------------------------ 

now, if user applies filter via slicer or via filter dropdown within pivottable or within powerpivot table excluding valuation dates < 2/1/2012, rank value should automatically update , reflected shown below:

(filters applied)

============================================================================= claim number | claimant number | transaction date |  dollar amount |  rank | ============================================================================ abcd123456           4                 8/1/2012           $205.12       1 abcd123456           4                 9/1/2012           $390.74       2 vdsf123455           2                 3/5/2012           $10.12        1 vdsf123455           2                 9/5/2012           $50.12        2   vdsf123455           2                12/9/2012          $210.45        3 =========================================================================== 

as can see, rank value automatically updated first claim group based on 1 of rows tied claim number abcd123456 being hidden, based on user applied filter.

i create additional measure within powerpivot table display dollar amount tied transaction containing rank value of 1 shown below:

(filters applied, new measure added)

=============================================================================================== claim number | claimant number | transaction date |  dollar amount |  rank | opening dollar amt| ================================================================================================ abcd123456          4                 8/1/2012          $205.12         1         $205.12 abcd123456          4                 9/1/2012          $390.74         2         vdsf123455          2                 3/5/2012          $10.12          1         $50.12 vdsf123455          2                 9/5/2012          $50.12          2  vdsf123455          2                 12/9/2012         $210.45         3 

===============================================================================================

a corresponding pivot table created referencing data "opening dollar amt" measure represent dollars tied first transaction per group outlined above.

i appreciate if can outline how accomplished.

i thought dynamic ranking approach might idea this, if has better idea achieve end result/goal obtain dollar amount tied first transaction "visible" (based on user applied filters) per group open ears whatever approach may have.

although written question you've put time formulating, should read cross posting in forums. clear duplication of you've posted on msdn @ same time. i've answered in both seeing decent question.

firstly created basic measure [amount] sum dollar amount column. used within rankx() create following:

[rank] = rankx(          filter(          allselected(table1),table1[claimant number]=max(table1[claimant number])                    ),          [amount],             ,1) 

the key table [amount] measure iterated on - allselected() brings stuff in current filter context play , expression within filter() restricts table current claim number.

after simple task return [amount] based on whether or not [rank] 1:

[opening balance] = if([rank]=1,[amount],blank()) 

hope makes sense, posted workings on skydrive if help.

jacob