Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
amitchandak
Super User
Super User

@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
Super User
Super User

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)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.