PowerPivot DAX: Identify Max & Min Value per Group

user1159554 picture user1159554 · Aug 15, 2013 · Viewed 38k times · Source

I am in need of identifying the maximum and minimum value per a defined group within a PowerPivot connected PivotTable.

Please reference the following base Excel table (posted via ASCII) that is linked to a corresponding PowerPivot table:

-------------------------------------------------
Customer   |  Store  |  Transaction Sequence No | 
-------------------------------------------------       
     A           1                  1
     A           1                  2
     A           1                  3
     A           2                  1
     A           2                  2
     B           1                  1
     B           1                  2
     B           2                  1

As one can see, there are 2 columns above that uniquely define a group

  1. Customer
  2. Store

For each group, there can be one or more transactions, each with an associated [Transaction Sequence No] value as also outlined above.

Within the resulting PivotTable via 2 DAX Measures, I would like to list the minimum and maximum [Transaction Sequence No] value tied to each unique group.

This would result in the following PivotTable:

----------------------------------------------------------------------------
Customer   |  Store  |  Transaction Sequence No |   Max eq No | Min Seq No |
---------------------------------------------------------------------------- 
     A           1                  1                  3           1
     A           1                  2                  3           1
     A           1                  3                  3           1
     A           2                  1                  2           1
     A           2                  2                  2           1
     B           1                  1                  2           1
     B           1                  2                  2           1
     B           2                  1                  1           1

Now, it would be important for both of these new DAX measures to be dynamically updated if a user via a slicer or filter decides to apply a filter against the [Transaction Sequence No] column.

In this example, if the user opts to exclude a [Transaction Sequence No] value of 3 via an applied filter, the PivotTable should have its data updated to reflect the new max and min values:

----------------------------------------------------------------------------
Customer   |  Store  |  Transaction Sequence No |   Max eq No | Min Seq No |
---------------------------------------------------------------------------- 
     A           1                  1                  2           1
     A           1                  2                  2           1
     A           2                  1                  2           1
     A           2                  2                  2           1
     B           1                  1                  2           1
     B           1                  2                  2           1
     B           2                  1                  1           1

I am having trouble formulating the proper DAX syntax to accomplish this.

Answer

greggyb picture greggyb · Oct 29, 2015
MinTSeq:=
CALCULATE(
    MIN('table'[Transaction Seq No])
    ALLSELECTED('table'[Transaction Seq No])
)

Should get you there. You can replace min with MAX().

More reading on ALLSELECTED().