How to Filter a Table To Show Only Most Recent Date For Each Project

[Anonymous]

I have a table to be loaded into Power BI. There are 3 columns I am concerned with: Project_Name, Document_Name, Modified. The data in the table is something like this:

|   Project_Name   |   Document_Name   |   Modified   |
+------------------+-------------------+--------------+
|   Thing1         |   Apple           |   12/28/2019 |
+------------------+-------------------+--------------+
|   Thing1         |   Orange          |   10/22/2019 |
+------------------+-------------------+--------------+
|   Thing2         |   Banana          |   12/17/2019 |
+------------------+-------------------+--------------+
|   Thing2         |   Banana          |   12/30/2019 |
+------------------+-------------------+--------------+

I want to filter the table such that the latest Modified date for each Project_Name shows, like this:

|   Project_Name   |   Document_Name   |   Modified   |
+------------------+-------------------+--------------+
|   Thing1         |   Apple           |   12/28/2019 |
+------------------+-------------------+--------------+
|   Thing2         |   Banana          |   12/30/2019 |
+------------------+-------------------+--------------+

I tried adding a column to the table, to add a True value to the most recent date for each project. I pretty much just copied a formula I found on these forums. It returned correct results but omitted many records. This was the formula:

Documents'[Modified]),FILTER(ALL('Project Documents'), 'Project Documents'[ProjectName]=EARLIER('Project Documents'[ProjectName])))

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Here we go:

Measure = 
VAR maxd =
    CALCULATE (
        MAX ( 'Table'[Modified] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[Project_Name] )
    )
RETURN
    IF ( MAX ( 'Table'[Modified] ) = maxd, 1, BLANK () )

Pbix as attached.

[amitchandak]

@Anonymous ,

Try like adding this measure to the visual

Measure = 
VAR __id = MAX ( 'Table'[project_name] )
VAR __date = CALCULATE ( MAX( 'Table'[Modified] ), ALLSELECTED ( 'Table' ),  'Table'[project_name] = __id ) 
RETURN CALCULATE ( count ( 'Table'[project_name] ), VALUES ( 'Table'[project_name ), 'Table'[project_name] = __id, 'Table'[Modified] = __date )

[Greg_Deckler]

You should be able to create a selector column or measure:

Column =
  VAR __Max = MAXX(FILTER('Table',[Project_Name] = EARLIER('Table'[Project_Name])),[Modified])

RETURN

  IF([Modified] = __Max,1,0)

Measure = 

  VAR __Max = MAXX(FILTER(ALL('Table'),[Project_Name = MAX('Table'[Project_Name]),[Modified])

RETURN

  IF(MAX([Modified]) = __Max,1,0)