Forum Discussion

Nickgastaldi's avatar
Nickgastaldi
Resolver I
7 years ago

Unexpected? filter behavior when translating to TSQL

Hello Comunity, 

 

long time following, first time posting, 

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.

 

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 :)

Need Help
Show and Tell

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I am not sure if this will work but you can try

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

Return
COUNTROWS(
  SUMMERIZE(
    tbl,
    myTable[Id]
  )
)
    • Nickgastaldi's avatar
      Nickgastaldi
      Resolver I
      7 years ago

      Hello Kristjan76

      thanks for your reply

       

      i did tried that , but exact same result, 

      for what i can see comparing PBI execution and DB log, the problem is not in the calculation itself but in the FILTER statement, its returning the exact same TSQL as i posted in question and no where clause is applied. 

       

      the calculation is not even sent to DB

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Nickgastaldi

    Try to create a measure as below

    "Home"->New measure

    Measure =
VAR last_time =
CALCULATE ( MAX ( [timetamp] ), ALL ( Sheet6 ) )
- 1 / 24
RETURN
CALCULATE (
DISTINCTCOUNT ( Sheet6[id] ),
FILTER ( ALL ( Sheet6 ), [timetamp] > last_time )
)

     

    PLease refer to thia article to know DAX functions supported for in DirectQuery mode.

    "DISTINCTCOUNT' function is Supported in measure and query formulas only.

     

     

    Best Reagrds

    Maggie

     

    • Nickgastaldi's avatar
      Nickgastaldi
      Resolver I
      7 years ago

      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 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I don´t know if this is going to help or if you have already tried but you can query the database directly, and perhaps you could decrease the amount of retrived data with a WHERE clause