How to get the latest status by id?

[shellacademic]

I have a table with Id, Status, DateTime amongst other columns.

I want to view a report of Id, LatestStatus. What is the best way to go about that?

[v-chuncz-msft · Accepted solution]

@shellacademic,

You may refer to the following DAX that creates a new table.

Table =
FILTER (
    Table1,
    RANKX (
        CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[Id] ) ),
        Table1[DateTime],
        ,
        DESC,
        DENSE
    )
        <= 1
)

[shellacademic]

Thanks @v-chuncz-msft I tried that but I get an error:

Fleshing out my problem a little more, I have imported the VSTS build information via the VSTS REST API into Power BI and have a table of Builds and BuildDefinitions. For each build definition I want to get the latest build information by looking at the max (BuildFinishTime).

What do I need to do to change my query?

Many thanks for your response.

[v-chuncz-msft]

@shellacademic,

I meant a calculated table.

[shellacademic]

Is there a way to do this using Direct Query? My data source has moved to a SQL Server database and I want to query the live data but now I can't create the calculated table. Is there a DAX equivalent or a way to have a calculated table with Direct Query?