Execute a parametric SQL query to get data filtered based on the values of a column or measure

[Farhad88]

Hi everyone!

Is it possible to execute a parametric SQL query on a data source to retrieve data filtered based on the values of a column, measure, or some specific values?

For example, I have a hierarchical dataset and I want to retreive the child nodes of the selected nodes (like this DAX code: Filter(mydataset, mydataset[ParentId]= {set of selected IDs})

I tried to use DAX functions (e.g., Filter and CalculateTable) to achieve my goal, but I couldn’t, since they have some limitations comparing to the SQL queries.

So, I want to execute the SQL query dynamically (in the runtime), or after clicking on a button, to save the result as a new table.

Thank you in advance for your suggestions!

[lbendlin]

 I want to execute the SQL query dynamically (in the runtime), or after clicking on a button, to save the result as a new table.

You cannot dynamically create new materialized tables.

Best you can do is Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

[Farhad88]

Dear @lbendlin ,

Thanks for your information.

It is sad, because DAX and Power query have some limitations, or maybe I need to learn more about them.

Currently, I have a Pro user license. Please let me know is there any better/faster way to get support, e.g., directly from Power BI support team?

Thanks and best regards,

 Farhad

[lbendlin]

Power BI is not designed for what you want to accomplish. It has no memory, no mutable variables and has no concept of data write-back. What you are looking for is something that supports the full information cycle. Maybe that is something you would have to develop yourself.