Unexpected? filter behavior when translating to TSQL

Hello v-juanli-msft

thank you for the reply 

i tried your DAX but same error. 

I am new to DAX , but i believe the problem is not the distinctcount part, but the last_time part.....

i am using profiller to check what PBI is sending to the database through directquery, 

and when it comes to this measure, all it does is try to do the last_time part, i tryed this : 

LastTime-Measure =
    VAR last_time =
        CALCULATE ( MAX ( [timetamp] ), ALL ( Sheet6 ) ) - 1 / 24

RETURN
       1

and the 1 won't ever be returned. 

what i see in database is that the MAX function is not being pushed to database, what it is doing is :

SELECT 
TOP (1000001) [t3].[timestamp]
FROM 
(
(select [$Table].[id] as [id],
    [$Table].[timestamp] as [timestamp],
    [$Table].[lat] as [lat],
    [$Table].[lon] as [lon],
    [$Table].[SimpleDate] as [SimpleDate]
from [dbo].[Location_Table] as [$Table])
)
 AS [t3]
GROUP BY [t3].[timestamp]

as you can see, this code is unfiltered, so this will never work cause this table has over 45M rows and time_stamp column is inserted every 2 seconds.

and this query returns over 1M rows, hence the code never works. 

any other ideas on how i can workaround this issue? 

all i really need is for dax query to pass sql a code like this:

SELECT 
  MAX(timestamp) 
 from Location_Table

Many thanks