latest date by id outputting country name measure help

[Anonymous]

I am looking to see if there is a simple way to take the latest date per ID and output what country that was visited by that ID base don the latest date. Below have put some sample data to better explain;

ID	Country	Date
1	Spain	01/01/2021
2	Italy	01/01/2021
3	France	01/01/2021
1	Ireland	02/01/2021
2	Denmark	02/01/2021
3	Sweden	02/01/2021

I then want to create a simple chart to say based on the axis being country how many ids sit in each country that are the latest visits to that country. This will need to be a measure as i need it to filter based on what date is selected. Originally i tried doing this by simply;

Measure =
VAR LatestDate =
MAXX(
KEEPFILTERS(VALUES('ID'[ID])),
CALCULATE(MAX('Date'[Date]))
)
RETURN
IF ( MIN ( 'Date'[Date] ) = LatestDate, 1, 0 )

but the issue i found with the above is it will only output a 1 or 0 where i would want to output the country name. Any help would trully be amazing.

[Anonymous]

@amitchandak please see link to your report which have added a large data sample to, as it seems it wont work in a bar chart when i do it on my set. Just gives me loads of duplicates could be to do with the additional id i have in the data?

https://we.tl/t-21kELQl9brhttps://wetransfer.com/downloads/cb0808d14fffbabf87293b7145ac8541202102081...

[amitchandak]

@Anonymous , Try like

Measure =
VAR LatestDate = MAXX(allselected('Date'),'Date'[Date])
RETURN
IF ( MIN ( 'Date'[Date] ) = LatestDate, 1, 0 )

[Anonymous]

thank you @amitchandak   but as mentioned above i don't want to output a 1 or 0 i want to output the country name and it needs to be the latest date per ID not latest date  for the country etc 

[amitchandak]

@Anonymous , Try like

Measure =
VAR LatestDate = MAXX(allselected('Date'),'Date'[Date])
RETURN
calculate(min(Table[country]) filter('Date', 'Date'[Date] = LatestDate))

[Anonymous]

@amitchandak i don't think that would work as my end output should look like this;

[amitchandak]

@Anonymous , Please share the output, if it does not match with file attached after signature

[Anonymous]

@amitchandak so what i am expecting is to display it like the following;

So based on what date you select it will sum up all the countries that have a max visit date for that id

[amitchandak]

@Anonymous , Try measure like

Measure 2 = VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id ) 
RETURN CALCULATE ( Count ('Table'[Country] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[Date] = __date )

[Anonymous]

@amitchandak seems when i add this into a chart showing country as axis and your measure as the value it doesnt give me the right part. Base don my full dataset it just shows me 2 results and there is around 100k different ids. In this are you filter to the max id?

[amitchandak]

@Anonymous , Check my file I was getting three countries in sample data.

File after signature

[Anonymous]

@amitchandak please see link to your report which have added a large data sample to, as it seems it wont work in a bar chart when i do it on my set. Just gives me loads of duplicates could be to do with the additional id i have in the data?

https://we.tl/t-21kELQl9brhttps://wetransfer.com/downloads/cb0808d14fffbabf87293b7145ac8541202102081...