How to take the latest row in a Power Bi table?

[united2win]

Hi,

I have the following table in one of my power bi reports:

Client	Country	Period	Year	Not Filed Reason	Status Change
Client A	DE	Jan	2019	Reaosn C	13/11/2019
Client A	DE	Jan	2019	Reason B	13/02/2019
Client A	DE	Jan	2019	Reason A	04/01/2018

I would like to be able to only return the lastest status in that table?

[Felix_Schulte]

Can someone please answer this simple question?

[united2win]

Here are the two tables in my power bi report:

Table 1:

Table 2:

On table 1 I have managed to merge the latest date using the concatenate column. I am only missing the associated reason... 😞

[v-shex-msft]

HI @united2win ,

Do you mean to add a column to 'table 1' to use current 'concatenate' field value to search corresponds max date from 'table 2'?

If this is a case, you can refer to the following calculated column:

Related Max Date =
CALCULATE (
    MAX ( Table2[Status Change] ),
    FILTER (
        ALLSELECTED ( Table2 ),
        [ConcateNate] = EARLIER ( Table1[Concatenate] )
    )
)

Regards,

Xiaoxin Sheng

[united2win]

Hi,

I have transferred the calculation and that given me the latest date, but I also need the corresponding reason. 

 

Seperately, I managed to get this by "Merging Queries" and using two variables the Date Change and the Concatenate. Would you say this is relaible?

[v-shex-msft]

HI @united2win ,

#1, You can use 'last date' and concentrate label to find out correspond reason from tabel2.

#2, Yes, it is reliable. You can take a look at the following blog about relationship with multiple columns:

Relationship in Power BI with Multiple Columns 

Regards,
Xiaoxin Sheng

[v-shex-msft]

HI @united2win ,

Maybe you can try to set up a filter with 'topn' mode on status change fields or write measure formula to get the last date based on category fields and compare with the current date to return tag, then apply on visual level filter.

Regards,

Xiaoxin Sheng

[united2win]

The reason why I need the latest status change is because I have another independent table, which I want to merge like this. 

Return Manager	Filed	Concantenate	Not Filed Reason	Status Change
Jim Slater	No	DE-Jan-2019	Reason C	13/11/2019

I will also add another coloumn in the previous table to concatenate the country, period & year in order to create a common key.

[Jorgast]

Have you tried determining the most recent date using the MAX function? 

Most Recent Change = Max (Table[Status Change])