Forum Discussion

aangers's avatar
aangers
Regular Visitor
2 years ago

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

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 ...
Need Help
aangers's avatar
aangers
Regular Visitor
2 years ago

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