Queries that change depending on panel?

[andyinv]

Hi

New to PBI, but struggling a little here - not sure what I should be searching for (and boy, have I been searching). MSP, got a lot of clients, a lot of tickets. They're in ConnectWise and all details pulled via the API.

However, if possible, I'd prefer not to have to pull thousands of tickets any time I refresh the data source - what I'd like to do instead is have a drill-down that submits the Get-Tickets API query based on the value of something in the panel itself (so Manage Parameters appears to be out of the question?). Eg, if in the pie-chart of "tickets by client", if we click on the client, it will then go refresh the datasource with "Get all the tickets for client, within the date range on that first panel", and pull them back.

Is this possible at all?

Thanks

[amitchandak]

@andyinv , is your source supported for the direct query?

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

[andyinv]

Thank you - didn't know of DirectQuery (the problem when you're dealing with new tech and don't know all the capabilities). Will research that for a bit.

As to how to get those parameters through to that DirectQuery from the currently visible panel - any suggestions to check out?

Thanks!

[amitchandak]

@andyinv , In case of direct Queries, queries to the backend. Example You can set one ticket as a slicer on the page. Of create a single select slicer. When you filter if data is not cached, it will send back to DB

[andyinv]

Ah, seems like I can't use DirectQuery on a web datasource tho - hence can't call my API. That's unfortunate... any way round that do you know? Only way I can see (so far) is with a paid ODBC->API connector...

[Anonymous · Accepted solution]

Hello @andyinv,

You seem to want to use the front data view level filter or slicers to interact with the back-end query table to dynamically change table records.
If that's the case, the current power doesn't support this. They are hosts at different data levels and the child level cannot be used to affect their parent.

If you only want to parameterize connections to use the selected parameter to affect their result, it is possible. You can create a query parameter and use it in the data connection steps. Next, you must modify the query parameter at the front and choose 'apply changes' to apply these operations to back-end queries. (they will activate the query table update to load and process it with modified formulas)

Drill down into Power BI query parameters and templates

Note: Power bi data level.

Database (external) -> query table (query, custom function, query parameters) -> data model table(table, calculate column/table) -> virtual data view table (measurement, visual, filter, segmentation)

Best regards

Xiaoxin Sheng