Creating a MAXIF in DAX

[ExcelWhizzDotCom]

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]

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.