topic Re: City from Date and ID in DAX Commands and Tips

City from Date and ID

f1rich — Fri, 25 Sep 2020 14:53:36 GMT

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

Re: City from Date and ID

camargos88 — Fri, 25 Sep 2020 15:09:43 GMT

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

Re: City from Date and ID

f1rich — Mon, 28 Sep 2020 08:18:17 GMT

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

Re: City from Date and ID

stevedep — Mon, 28 Sep 2020 19:11:26 GMT

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.

Re: City from Date and ID

f1rich — Mon, 28 Sep 2020 10:04:23 GMT

Thank you so much stevedep ! 😀

I have been trying to do this for 2 days 😱

Re: City from Date and ID

stevedep — Mon, 28 Sep 2020 19:12:42 GMT

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