Direct Query + Snowflake resulting in queries with cast as date for every unique date

[aangers]

When generating reports using DirectQuery with a Snowflake Connector, I am encountering badly transposed queries in Snowflake for queries where the data is filtered on a Date or Date/Time range. The queries will have a CAST as DATE for every unique Date, or a TO_TIMESTAMP for every unique Date/Time entry. Since our data covers many years, the queries get very long and complex for filtered date ranges. Yet, the date variable in my data is set to Date format in Power BI as well as in Snowflake.

Is there any way to optimize the query and make sure that the date variable is well integrated in the query?

 

[enjoytheride]

I'm running into the same problem, did you happen to find a solution?

[aangers]

Nope haven't found a solution unfortunately

[sofia_pc]

I'm having the same issue. Did you find a solution?

[aangers]

I have not unfortunately...

[aangers]

Thanks for the response.

I've read about Value.NativeQuery and tried to apply this to the Power Query configuration of my Snowflake connexion. Here's the full code in the Advanced Editor. I have no other steps, all other data processing steps are made in DAX calculated measures.

let

Source = Snowflake.Databases("XXXX","WH_XXXX"),
DB_XXXX = Source{[Name="DB_XXXX",Kind="Database"]}[Data],
SCHEMA_XXXX = DB_XXXX{[Name="SCHEMA_XXXX",Kind="Schema"]}[Data],
MY_TABLE = SCHEMA_XXXX{[Name="MYTABLE",Kind="Table"]}[Data],
SqlStatement = "SELECT * FROM DB_XXXX.SCHEMA_XXXX.MYTABLE",
NativeQuery = Value.NativeQuery(MY_TABLE, SqlStatement, null, [EnableFolding=true])

in

NativeQuery

However, when I apply this, I get the following error 

Expression.Error: Native queries aren't supported by this value.
Details:

[Table]

Do you know what would cause this error?

Secondly, I also checked all my DAX measures so that when dates are filtered, it is done with ">=" instead of using "IN {range}". None of my DAX measures had an "IN {range}" statement.

Here is one of my DAX measures, maybe I'm not seeing something?

my_measure =

VAR metric = SELECTEDVALUE(Table[ID])

VAR ThisDate = SWITCH(TRUE(),

    metric = "LASTWKPCT", MAX(Series1[EndDate]),

    metric = "LASTMTHPCT", MAX(Series2[StartDate]),

    metric = "WKYOYPCT", MAX(Series1[EndDate]),

    metric = "MTHYOYPCT", MAX(Series2[StartDate])

)

VAR my_list = DISTINCT(SELECTCOLUMNS(FILTER(Series1, Series1[EndDate] = ThisDate && NOT ISBLANK(Series1[Count])), "SiteID", [SiteID]))

VAR my_list_m= DISTINCT(SELECTCOLUMNS(FILTER(Series2, Series2[StartDate] = ThisDate && NOT ISBLANK(Series2[Count])), "SiteID", [SiteID]))

VAR Vol_ThisDate = SWITCH(TRUE(),

    metric = "LASTWKPCT", CALCULATE(SUM(Series1[Count]), Series1[Categ]=1),

    metric = "LASTMTHPCT", CALCULATE(SUM(Series2[Count]), Series2[Categ]=1),

    metric = "WKYOYPCT", CALCULATE(SUM(Series1[Count]), Series1[Categ]=1),

    metric = "MTHYOYPCT", CALCULATE(SUM(Series2[Count]), Series2[Categ]=1)

)

VAR Vol_PrevDate = SWITCH(TRUE(),

    metric = "LASTWKPCT", CALCULATE(SUM(Series1[Count2]), Series1[Categ]=1 && Series1[SiteID] IN my_list),

    metric = "LASTMTHPCT", CALCULATE(SUM(Series2[Count2]), Series2[Categ]=1 && Series2[SiteID] IN my_list_m),

    metric = "WKYOYPCT", CALCULATE(SUM(Series1[Count2]), Series1[Categ]=1 && Series1[SiteID] IN my_list),

    metric = "MTHYOYPCT", CALCULATE(SUM(Series2[Count2]), Series2[Categ]=1 && Series2[SiteID] IN my_list_m)

)

RETURN SWITCH(TRUE(),

    ThisDate < [DATE_PrevDate] || ThisDate > [DATE_ThisDate], BLANK(),

    Vol_ThisDate > 0 && Vol_PrevDate > 0, DIVIDE(Vol_ThisDate - Vol_PrevDate, Vol_PrevDate)

)

[lbendlin]

You need to run your native query against the database, not the table.

[aangers]

Sorry I misunderstood how to apply the Value.NativeQuery. Here is how I managed to bypass this error : 

= Value.NativeQuery(Snowflake.Databases("XXXX","WH_XXXX"){[Name="DB_XXXX"]}[Data], "SELECT * FROM ""SCHEMA_XXXX"".""MYTABLE""", null, [EnableFolding=true])

However, the problem persists, the queries sent to snowflake still have the "cast as date"... Do you know how to control the queries that are created and sent to Snowflake from DAX calculated measures?

[lbendlin]

See if you can use dynamic M Query parameters.

Chris Webb's BI Blog: Using Power BI Dynamic M Parameters In DAX Queries (crossjoin.co.uk)

Passing Any Arbitrary Value From A Power BI Report To A Dynamic M Parameter (crossjoin.co.uk)

[lbendlin]

great question.  Please read about Value.NativeQuery  (and its folding options).  BUT - you will also want to change your query from   "[Date] IN {Range}  " to "[Date] >= MinDate && [Date]<= MaxDate"

What you have hit upon is a frequent issue in composite models where the join column cardinality is too high. They use a similar set based query which can result in truly ginormous queries - I had a case where the query text (just the text!!!) was 6GB.  FOR. EVERY. SINGLE. QUERY.