CODEWITHSUNDEEP
visual-studio-2012maxpowerpivotcalculated-columnsdax
Saith
Saith

Reputation: 3

DAX for MAX of a group

I have two data columns:

Record | Record Version
------------------------
1      | 1
1      | 2
1      | 3
2      | 1
2      | 2
2      | 3
2      | 4
3      | 1
3      | 2
3      | 3
4      | 1
4      | 2
4      | 3
4      | 4
4      | 5
4      | 6
5      | 1
6      | 1

Is it possible to create a calculated column within a MVS-2012 model or PowerPivot that will give the MAX of the Record Version for a corresponding Record? For example the MAX value of Record 1 is 3, MAX value of Record 2 is 4 etc.

Upvotes: 0

Views: 2437

Answers (2)

Jacob
Jacob

Reputation: 3587

Generally, as @mmarie suggests, you are better dealing with this kind of problem with a measure rather than a calculated column. That said, sometimes you just need that extra column (usually to use as a dimension).

Assuming you have a table called recordsFact, this gives the MAX of that record:

= CALCULATE(MAX(recordsFact[Record Version]), 
             FILTER(recordsFact, recordsFact[Record] = EARLIER(recordsFact[Record])
                   ))

Upvotes: 0

mmarie
mmarie

Reputation: 5638

Yes. Add that table to your Power Pivot model, either by pasting the data into Excel and then clicking Add to Data Model on the Power Pivot ribbon, or by opening the Power Pivot Window and adding it form it's original source there.

  1. In the Power Pivot window, go to the table where this data is located.
  2. Select the Record Version column, then click the drop-down next to AutoSum in the Calculations group on the Home tab and choose Max. enter image description here
  3. A calculated column will appear at the bottom. YOu can rename it to something like Max Record Version.
  4. Make a PivotTable in Excel (click the PivotTable button on the Home tab).
  5. Add the Record field to rows and the Max Record Version to values.

enter image description here

If you actually want a calculation that will give you the max for the group of record you could do something like: 

=SUMX(VALUES(Table1[Record])
      ,CALCULATE(MAX(Table1[Record Version]))
     )

Upvotes: 3

Related Questions