Pulling multiple SQL tables with Python

[alexestr]

I am using Python as a source to get dynamic user input before pulling data. I use a popup window for the user to search and select a client then a contract for that client. I have this part working, however when running through Power BI, it seems to run the python script several times indicated by the popup appearing multiple times. And each window needs to be interacted with in order to successfully retrieve data. I am not sure if this is a symptom of four seperate tables being retrieved, but it is rather clunky and there must be a way to limit it. 

Here are the dataframes being returned to Power bi:

[lbendlin]

Do not do that.

https://blog.crossjoin.co.uk/2020/07/05/why-is-power-bi-running-my-sql-query-twice/

[alexestr]

Do you have a recommendation for accepting user input? I am fairly new to Power bi and there doesn't seem to be much for getting user input to narrow search results. The user is unlikely to know the exact name of the desired field, so first i have to let them type in an approximation, then return possible options, from there they pick the correct one and then the rest of the queries use that data to return a dataset within Power bi. Slicers are not desireable, as the original table will have thousands of options to choose from, and that is way too many to sift through. Additionally, each one of those options will have thousands of records attached to it. I cannot bring it all in, I need to trim the result set before bringing it into Power BI.

[lbendlin]

Power BI is not a dynamic OLAP cube environment. If you need that, use a dynamic OLAP cube environment.

Here are a couple of suggestions:

- Enable "Personalize Visuals" and train your users on how to use that

- Evaluate the use of Field Parameters

- Look into Dynamic M query parameters (only applicable for Direct Query)