To show the latest location on map

[hongyuliu]

I have a machine working in Germany last month and working in France this month. How to show only France on Map.

Machine Type	Location	Date
Truck 001	Germany	2019/11/11
Truck 001	France	2019/12/12

My data table is like above. Thank you very much for your help.

[rajulshah · Accepted solution]

You haven't mentioned FILTER in the formula.
See below:
CALCULATE(MIN(Maps[Location]),FILTER(Maps,Maps[Date]=LatestDate && Maps[Location]=Location))

The higlighted part needs to be in the query.

[v-jayw-msft · Accepted solution]

Hi @hongyuliu ,

Please refer to the calculated culumn below and see if the result achieve your expectation.

Column = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[Machine Type] = EARLIER ( 'Table'[Machine Type] ) )
    )
RETURN
    IF ( 'Table'[Date] = a, 'Table'[Location], BLANK () )

Here's my sample data and the result would be shown as below.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajulshah]

Hello @hongyuliu ,

You can create a calculated column that fetches only the latest location and then use that column in the map visual.

Hope this helps.

Let me know if this didn't help.

[hongyuliu]

Hi

Thanks for your reply.

What's the dax function for this?

[rajulshah]

Hey, 

You can use the following DAX function here:

Latest Location = 
VAR LatestDate = CALCULATE(LASTDATE(Maps[Date]),FILTER(Maps,Maps[Machine Type]=EARLIER(Maps[Machine Type])))
VAR Location = Maps[Location]
RETURN CALCULATE(MIN(Maps[Location]),FILTER(Maps,Maps[Date]=LatestDate && Maps[Location]=Location))

Please see the following image:

Hope this helps.

[hongyuliu]

Hi,

Please have a look at above pic. I followed your steps, but got error. what's the problem?

[rajulshah · Accepted solution]

You haven't mentioned FILTER in the formula.
See below:
CALCULATE(MIN(Maps[Location]),FILTER(Maps,Maps[Date]=LatestDate && Maps[Location]=Location))

The higlighted part needs to be in the query.