Forum Discussion
Snowflake Custom SQL query with parameters not returning results
Hi Jbigby ,
Thanks for reaching out to the Microsoft fabric community forum.
For a DirectQuery model table, the query must be a SELECT statement, and it can't use Common Table Expressions (CTEs) or a stored procedure.
Source: Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn
This might be the reason for the error you are facing.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you
Hi v-nmadadi-msft - I've removed the CTEs and just used a standard SELECT statement. I am still not seeing results in the report. I also tried the URL and no results were returned.
Hi Jbigby ,
Most likely the root cause of thee error is your query is not getting folded down and isn’t reaching the source database with the correct query.
Please follow this article and try to test query folding and troubleshoot it:
Query folding on native queries - Power Query | Microsoft Learn
Also the and operator is not supported in dynamic M query operations
Source: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank youHi v-nmadadi-msft ,
The issue still isn't resolved. I've removed the CTE. Query folding is enabled with custom query. I do not see the option to bind the parameter.
I had a call earlier with Microsoft and was sent a link for free consulting with OmniData Insights. I will set up time to work with them.
Hi Jbigby ,
We really appreciate your efforts and for letting us know the update on the issue.
Please keep us posted with any resolution that you may reach with them.Additionally continue using fabric community forum for your further assistance.
Thank you