Snowflake Custom SQL query with parameters not returning results

[Jbigby]

Is there a way to dynamically filter a Snowflake custom SQL statement by using parameters via URL input? 

I am building Power BI report with the below requirements. The query works if I remove the last filters for parameters. However, I cannot bring in the entire dataset and need to be able to dynamically filter the query based upon the 2 parameters. The issues and current state details are below below. Please help.

Issues:

• No results are returned
• Cannot filter via URL
• Do not see the option to Bind To Parameter

Requirements:

• Power BI Pro
• Use a service account for Snowflake connection.
• No Power BI Gateway
• DirectQuery Mode to bring in from Snowflake
• The URL parameters need to filter the SQL query
  • entity_table is a column (ex: account)
  • object_id is a GUID (ex: 123abcd1a-1234-ab12-a123-000a1b2cde3f)
• Don’t return any records unless a valid parameter is passed.
• Embed into Dynamics CE from a Power BI Workspace, with dynamic filtering.
• Ability to test functionality before handing off for embedding.

Current State:

• Power BI report is published to Power BI workspace.
• Connecting to Snowflake via Custom SQL query with CTEs, with filters for parameters at the end.
• Includes parameters for par_entity_table and par_object_id, both with text format.
  • I've tried setting up both with and without a current value listed. Neither work.

Custom SQL query:

WITH cte_rpt_audit_v AS (

.... joins and aliases... 

)

SELECT *

  FROM cte_rpt_audit_v

 WHERE entity_table = ‘{{par_entity_table}}’

   AND object_id = ‘{{par_object_id}}’

Advanced Editor for Snowflake table:

Parameter Settings:

[v-nmadadi-msft]

Hi @Jbigby 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft]

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

[Jbigby]

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.

[v-nmadadi-msft]

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 you

[Jbigby]

Hi @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.

[v-nmadadi-msft]

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