Dax Filter trying to import table instead of filtering in database

[Nickgastaldi]

I am not sure if i lack the necessary knowledge but i am facing what i consider a strange behaviour in one of my dashboards,

here is the thing:

i have a very simple table , basically 2 columns, ID, TimeStamp , that logs info for a decent amount of devices , this table has 49m rows , and grow on a 2minutes basis, i am using lastest PBI and lastest SQLServer, and directquery mode

now what i want to do is count the amount of unique IDs in the last hour, and compare to same time in last week and last month, i know and this is currently how i am doing, i can do this easily through SQL, and just fetch the results from a new table in SQL, but i wanted to have this in DAX-PBI for flexibility and cross-filter.

so if i do this:

online today - Simply = 
distinctcount( myTimeTable[id] )

PBI is pushing to SQL a query equivalent to:

Select COUNT(distinct id)
from MyTableTable

and returning 24k ids. expected.

however if i try to filter table to my desired result set

ids last hour = 
VAR LastStamp = MAX( myTable [timestamp] ) - (1/24) -- last update minus 1 hour. (this works fine)

Return
    CALCULATE(
        DISTINCTCOUNT( myTable[id] ),
        FILTER( myTable, myTable [timeStamp] > LastStamp)
        )

PBI is sending to DB a query translated like this:

SELECT 
TOP (1000001) [t3].[timestamp]
FROM 
(
(select [$Table].[id] as [id],
    [$Table].[timestamp] as [timestamp], -- this is a datetime column
    [$Table].[SimpleDate] as [SimpleDate] --this is a date column
from [dbo].[myTable] as [$Table])
)
 AS [t3]
GROUP BY [t3].[timestamp]

and this query returns over 1M rows and ends up with limit query size error in PBI end.

I Also tried sending the table to VAR and same result, the expression doesnt reach the calculation so my believe is that the FILTER is not being applied and PBI is trying to import the table and filter it in CE (locally) instead of pushing the calculation to SE (Database).

now my question is, why? 
why the filter statement is not been passed as a "where" clause , and why is it trying to group the timestamps and not just send the calculation to SE instead of trying to bring everything to CE ? 

and can someone please help me out getting this to work in DAX and help me understand why this is happening when other queries translate just fine? as i said, i already have a TSQL solution, but i wanted to have it in DAX for crossfiltering and other PBI resources. 

Big thanks in advance 

[v-danhe-msft]

Hi @Nickgastaldi,

Could you please post some sample data and your desired result if possible?

Regards,

Daniel He

[Nickgastaldi]

Any helps or thoughts? please?