Run multiple queries during drilldown and use selected elements from visual in SQL-where clause
We are trying to use Power BI Desktop / Power BI report server to display results from a big data warehouse realized in postgres. I am working on views to be used with Power BI, either with <DIRECT QUERY> or <IMPORT MODE>. Now my two questions are : 1) Is it only possible to run database Queries if one opens a power bi report or is it also possible to run additional queries later during drilldowns from one page in a report to another page ? 2) During drilldown people normally select an element in a visual lets say with transaction monitoring results. Then I would like to use the selected element for example in a matrix visual as input for the where clause of another query which gives results of the next drilldown level. Is it possible ? Example for better understanding: I use query SELECT * FROM VIEW_WITH_WEEKLY_RESULTS . Then someone select the visual element with WORKFLOW = Workflow_7 and ISO_WEEK_OF_YEAR = '2022-26' Now during drilldown I would like to run another SQL-statement like : SELECT * FROM VIEW_WITH_DAILY_RESULTS WHERE WORKFLOW = 'Workflow_7' AND ISO_WEEK_OF_YEAR = '2022-26' The idea is to reduce number of rows which will be returned from the query. Also there are a lot of dimensions, where drilldown shall be possible Currently I am using Views like VIEW_WITH_WEEKLY_DAILY_RESULTS VIEW_WITH_WEEKLY_DAILY_HOURLY_RESULTS which is very ineffective and gives bad performance even in medium size test environments. I would like to have Views VIEW_WITH_WEEKLY_RESULTS VIEW_WITH_DAILY_RESULTS VIEW_WITH_HOURLY_RESULTS VIEW_WITH_INSTANCE_DATA but only the first view shall be loaded completely to the power bi report , the other ones shall be used only with where clauses reducing the data. I tried to use parameters, but was not able to get them into the where-clause of a query. From my background I am a database guy, not a power bi specialist.