Forum Discussion
Dynamic Parameters on Snowflake - can not bind with other date table
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
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