How to I use the MAX function in power bi only on the filtered records?

Question

I am trying to select the record with the latest date, all of the records in the database have these basic columns AssetNumber, WorkOrderNumber, ScheduledEndDate, Department

I want to find all the latest work order "date" for each asset in a specific department. I have a basic measure and column to do this but it is not working.

How do I filter the records and then apply the max date function. I have tried using ALL, ALLEXCEPT, ALLSELECTED etc.

| ASSET | DEPARTMENT | WOSCHED_ENDDATE |
|-------|------------|-----------------|
| 2160  | 57257E     | 11/29/2011      |
| 2160  | 57257E     | 7/28/2014       |
| 6440  | 57257E     | 3/5/2012        |
| 6440  | 57257E     | 3/9/2015        |

At the basic level I need to remove the two records with the old dates from my visual. I can find the most recent (MAX) date of the entire column but I cannot get the MAX function to work in the context of the filtered values which by department like I have in the sample grid.

Thank you

Show the expressions you have tried. Also explain how you are filtering the data. — alejandro zuleta
Measure-- MaxDate = CALCULATE(MAX('Data_Mart'[WOSCHED_ENDDATE].[Date]), VALUES('Data_Mart'[ASSET])) The Column is as follows, IsLast = if(maxDate = (data_Mart[WOSCHED_ENDDATE].DATE]), "IS LAST",'')) — user3094203
I am trying to filter using the slicer on the report page. This should be very simple, I just want the max function to filter in the context of the slicer. — user3094203
I have used this link as the basis of my approach, this works fine in the context of the entire table, but I cannot figure out how to reduce the records that the max function is looking at. I hope this helps. — user3094203
Are you adding WOSCHED_ENDATE column to the Values table? If so delete it from there leaving only ASSET and DEPARTMENT column, then create a measure MaxDate=MAX(WOSCHED_ENDATE) and add it to the values table. See my answer update. — alejandro zuleta

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-11-09T19:46:02

Try creating a measure:

MaxDate =
CALCULATE (
    MAX ( 'Data_Mart'[WOSCHED_ENDDATE] ),
    ALLSELECTED ( 'Data_Mart'[WOSCHED_ENDDATE] )
)

I think the simple:

MaxDate = MAX( 'Data_Mart'[WOSCHED_ENDDATE] ) should work.

In Power BI using a matrix I get:

Note WOSCHED_ENDDATE is not included in the matrix if you include it the measure will be evaluated per each row so the MaxDate measure will give the same date in each row.

Let me know if this helps.

How to I use the MAX function in power bi only on the filtered records?

1 Answers