Calculate Max date by filter two columns

[Saxon10]

I have a two tables are data and report
In data table contain item, country, date, time and area code
In data table country column has India, Pak, US, UK, AUS, SRI, CAND and NEWL.
In report table contain item only (item as a duplicated)
I am looking for max date based on the following criteria: 1. Max time 2. Country is {India,US,UK,NEWL} and rest of the country code need to be ignore 3. area code {A} only.
If items is exist then return max date based on the max time column and filter by certain of country {India,US,UK,NEWL} and area code is {A} only.
Each item must be has 4 Country {India,US,UK,NEWL} if not then return blanks (Example -30123 ,3333)
DATA TABLE
ITEM	COUNTRY	DATE	TIME	AREA CODE
123	INDIA	02/04/2021	23:15:00	A
123	PAK	02/04/2021	23:15:00	A
123	US	02/04/2021	23:15:00	A
123	UK	02/04/2021	23:15:00	A
123	AUS	02/04/2021	23:15:00	A
123	SRI	02/04/2021	23:15:00	A
123	CAND	02/04/2021	23:15:00	A
123	NEWL	02/04/2021	23:15:00	A
123	INDIA	23/03/2021	23:15:00	A
123	PAK	23/03/2021	23:15:00	A
123	US	23/03/2021	23:15:00	A
123	UK	23/03/2021	23:15:00	A
123	AUS	23/03/2021	23:15:00	A
123	SRI	23/03/2021	23:15:00	A
123	CAND	23/03/2021	23:15:00	A
123	NEWL	23/03/2021	23:15:00	A
123		23/03/2021	23:10:00	B
1235	INDIA	02/04/2021	23:15:00	A
1235	NEWL	02/04/2021	23:15:00	A
1235	US	02/04/2021	23:15:00	A
1235	UK	02/04/2021	23:15:00	A
1235	INDIA	28/03/2021	23:15:00	A
1235	AUS	28/03/2021	23:15:00	A
1235	US	28/03/2021	23:15:00	A
1235	UK	28/03/2021	23:15:00	A
6789	INDIA	05/04/2021	23:15:00	A
6789	PAK	05/04/2021	23:15:00	A
6789	US	05/04/2021	23:15:00	A
6789	UK	05/04/2021	23:15:00	A
6789	AUS	05/04/2021	23:15:00	A
6789	SRI	05/04/2021	23:15:00	A
6789	CAND	05/04/2021	23:15:00	A
6789	NEWL	05/04/2021	23:15:00	A
4342	INDIA	03/04/2021	23:15:00	A
4342	NEWL	03/04/2021	23:15:00	A
4342	US	03/04/2021	23:15:00	A
4342	UK	03/04/2021	23:15:00	A
1237	INDIA	04/04/2021	10:19:00	A
1237	PAK	04/04/2021	10:19:00	A
1237	US	04/04/2021	10:19:00	A
1237	UK	04/04/2021	10:19:00	A
1237	AUS	04/04/2021	10:19:00	A
1237	SRI	04/04/2021	10:19:00	A
1237	CAND	04/04/2021	10:19:00	A
1237	NEWL	04/04/2021	10:19:00	A
1237		02/04/2021	10:16:00	B
30123	INDIA	02/04/2021	23:15:00	A
30123	US	02/04/2021	23:15:00	A
30123		02/04/2021	23:15:00	B
3333	INDIA	02/04/2021	23:15:00	A
3333	NEWL	02/04/2021	23:15:00	A
3333		02/04/2021	23:15:00	B

REPORT TABLE
ITEM	MAX DATE(DESIRED RESULT)
123	02/04/2021
12345	02/04/2021
30123
1235	02/04/2021
6789	05/04/2021
4342	03/04/2021
3333

[Jihwan_Kim]

Hi, @Saxon10 

Please check the below measure and the sample pbix file's link whether it is what you are looking for.

https://www.dropbox.com/s/hjra12fkfkixrvl/saxon10.pbix?dl=0 

Max Date =
VAR countriescondition =
VALUES ( Data[COUNTRY] )
VAR newtable =
FILTER (
Data,
Data[COUNTRY]
IN { "INDIA", "US", "UK", "NEWL" }
&& Data[AREA CODE] IN { "A" }
)
RETURN
IF ( COUNTROWS ( countriescondition ) >= 4, MAXX ( newtable, Data[DATE] ), "" )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Saxon10]

Hi,

Thanks for your reply.

Your solution working well. I would like to get the same result by using New calculated column.

Can you please advise me why the item column not mentioned in your measure? How it pick actual result without item column range?

Herewith attached the PBI file for your reference. (Data and report file)

https://www.dropbox.com/s/prtdxmt7whfr8kc/saxon10.pbix?dl=0

[Jihwan_Kim · Accepted solution]

Hi, @Saxon10 

Please check the below-updated link that includes the calculated column.

https://www.dropbox.com/s/hjra12fkfkixrvl/saxon10.pbix?dl=0 

Max Date new column =
VAR countriescondition =
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] ), Data[COUNTRY] )
VAR newtable =
FILTER (
Data,
Data[ITEM] = Report[ITEM]
&& Data[COUNTRY]
IN { "INDIA", "US", "UK", "NEWL" }
&& Data[AREA CODE] IN { "A" }
)
RETURN
IF (
COUNTROWS ( countriescondition ) >= 4,
MAXX ( newtable, Data[DATE] ),
BLANK ()
)

If you write calculated measure, the table visualization already has the filter for the item, so I did not mention the item column in the calculated measure.

However, as you can see in the calculated column, I had to mention the item column.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Saxon10]

Thanks for your reply and help. Your solution working well. 

[Saxon10]

Thanks for your reply and help. I will check and come back to you.

I am looking for New calculate column option can you please advise How can I get that.