Dynamic Parameters on Snowflake - can not bind with other date table

[arie_heutmekers]

Hi,

I have create a Query which gets data from Snowflake using the directquery connection. 

I have create a parameter (enddate) that is used in the SQL statement to get the date from Snowflake.

I have a table that shows the SF data.

I use Power BI version Sept 2022.

Sofar everything works.

I have create a new calendar table that contains one column with dates. In the PBI model section I have now bind the date column to the enddate parameter so user can select a date using a slicer. Once I do that, I get following error in the SF data table:

Direct Query error: DirectQuery may not be used with this data source. Please consider moving to supported data source or upgrading the SQL Server data source to the latet available version.

Did I missed something or can dynamic parameters not be transfered to SF?

[Anonymous]

Hi @arie_heutmekers ,

What is the data type of this date column? If it is a date/time type, please nest DateTime.Date() outside the parameter.

If your parameter is of Date/Time data type, you will need to cast it within the M query as DateTime.Date(<YourDateParameter>)

Dynamic M query parameters in Power BI Desktop

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[arie_heutmekers]

Hi Gao,

Thanks for your reply.

Both the source field and the parameter are defined as a DATE field.

Also the PBI calendar table that is used for binding the parameter is also a date field.

This is (part of) the SQL statement I used.

PS: this code works fine using parameters but once I bind the parameters to a calendar table in PBI, I get the error: Direct Query error: DirectQuery may not be used with this data source.

SELECT
kd.DAY as KEY_DATE,
kd.FIRST_DAY_IN_FISCAL_MONTH FD_MTH,
kd.FIRST__DAY_IN_FISCAL_QTR FD_QTR,
kd.FISCAL_QTR_TOTAL_WORK_DAYS WD_QTR,
kd.FISCAL_QTR || ' ' || kd.FISCAL_YEAR QTR,
m1.FIRST_DAY FD_M1,
m1.LAST_DAY LD_M1,
m1.TOTAL_WORK_DAYS WD_M1,
m2.FIRST_DAY FD_M2,
m2.LAST_DAY LD_M2,
m2.TOTAL_WORK_DAYS WD_M2,
m3.FIRST_DAY FD_M3,
m3.LAST_DAY LD_M3,
m3.TOTAL_WORK_DAYS WD_M3
FROM XXXX.FIN.FIS_CALENDAR kd
LEFT JOIN XXXX.FIN.FIS_CALENDAR fd
ON fd.CALENDAR_KEY = to_char(kd.FIRST__DAY_IN_FISCAL_QTR,'YYYYMMDD')::INT
LEFT JOIN XXXX.FIN.FIS_CALENDAR m1
ON m1.CALENDAR_KEY = fd.FISCAL_MONTH_CALENDAR_KEY
LEFT JOIN XXXX.FIN.FIS_CALENDAR m2
ON m2.CALENDAR_KEY = m1.CALENDAR_KEY + 1
LEFT JOIN XXXXX.FIN.FIS_CALENDAR m3
ON m3.CALENDAR_KEY = m2.CALENDAR_KEY + 1
WHERE kd.CALENDAR_KEY = to_char('"&Date.ToText(END_DATE,[Format = "yyyyMMdd"])&"')::INT ) cal
WHERE gl.POST_DT BETWEEN cal.FD_QTR AND cal.KEY_DATE

Thanks again for your help.

Kind regards,

Arie Heutmekers