Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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])))

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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)

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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's avatar
    amitchandak
    Super User
    6 years ago

    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 )