CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
JayCo741
JayCo741

Reputation: 603

Power BI: 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 MS forums. It returned correct results but omitted many records. This was the formula:

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

I also looked at this question that seemed to be after the same thing - but no real solution was posted. But it seems that I might have better luck filtering at the table level than calculating a new column.

Upvotes: 1

Views: 25874

Answers (1)

JayCo741
JayCo741

Reputation: 603

I cross-posted this to PowerBI Community and the following worked. Creating a measure on the table, rather than a filter, that I can use on the visual to effectively filter the rows. The measure returns a 1 for each latest date per project name. I can then filter the visual on the measure.

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

Upvotes: 3

Related Questions