# Creating a MAXIF in DAX



## ExcelWhizzDotCom (Mar 1, 2019)

Hi guys,

I'm really struggling to get this to filter as I wish using CALCULATE. Can anyone help with correct DAX, or offer any alternative methods of achieving this in Power Pivot?

The relevant fields in my dataset are: SiteID, AssessmentDate, Grade. I only want my model to to filter for the last graded assessment at each site.

Therefore I'm trying to create a column flagging the max AssessmentDate for each SiteID but only where [Grade]<>"Ungraded".

Any advice would be gratefully received!

Regards
EW


----------



## macfuller (Mar 8, 2019)

I'm not clear on your final result - are you looking for the grade given for the latest not-ungraded assessment date?  If all you want is to put the last relevant assessment date into every row of the table you can try as a new column formula

```
NewColumn=CALCULATE (
    MAX ( [AssessmentDate] ),
    FILTER (
        ALL ( YourTableName ),
        [SiteID] = EARLIER ( [SiteID] )
            && [Grade] <> "Ungraded"
    )
)
```

If you want the grade associated with that last assessment date for the site then you'd need a VALUES to pull the grade, assuming that only one grade is associated with each assessment date per site ID.  That could be a measure rather than a column but you'd need to provide more info.


----------

