City from Date and ID

[f1rich]

Hi guys,

Hope you can help me here..... I have been looking in google to find a dax formula with no luck.

What i am trying to do is create a dax column for my visual table to show the city from the lowest Date for each ID - see example below

Is this possible?

ID	Date	City	Column
55555	01/01/20 10:34:36	Leeds	Leeds
55555	01/01/20 10:35:02	Sheffield	Leeds
66666	02/01/20 00:12:40	Bristol	Bristol
66666	02/01/20 00:12: 45	Bradford	Bristol
66666	02/01/20 00:12:57	Bradford	Bristol

[stevedep · Accepted solution]

Hi,

This should do the trick:

___firstCity = 
var _firstDate = CALCULATE(MIN([Date]), ALLEXCEPT('Table','Table'[ID]))
return
CALCULATE(MIN('Table'[City]), FILTER(ALLEXCEPT('Table','Table'[ID]),[Date]=_firstDate))

as seen here:

As demonstrated here. 

the file available here. 

Please mark as a solution if so. Thumbs up for the effort are appreciated.

Kind regards,

Steve. 

[f1rich]

Thank you so much @stevedep ! 😀

I have been trying to do this for 2 days 😱

[stevedep]

Welcome! Keep learning, it gets more easy over time. 

[camargos88]

Hi @f1rich ,

Try this measure:

NewColumn = 
VAR _minDate = CALCULATE(MIN([Date]), ALLEXCEPT('Table', 'Table'[D]))
VAR _minCity = CALCULATE(MAX('Table'[City]), FILTER(ALLEXCEPT('Table', 'Table'[D]), [Date] = _minDate))
RETURN _minCity

[f1rich]

Morning @camargos88 

Unfortunately the fomula is not working because it is picking the last letter from City not from the date and ID - see example below

ID	Date	City	Column
77777	05/01/2020 20:36:22	Rotherham	Sheffield
77777	05/01/2020 20:36:25	Sheffield	Sheffield

What I need is to show the first city from the earliest date from the same ID - see below

ID	Date	City	Column
66666	05/01/2020 19:02:03	Barnsley	Barnsley
66666	05/01/2020 19:02:06	Cudworth	Barnsley
77777	05/01/2020 20:36:22	Rotherham	Rotherham
77777	05/01/2020 20:36:25	Sheffield	Rotherham
88888	05/01/2020 21:04:34	Bristol	Bristol

Thank you