Great question—and yes, this is definitely possible with some setup in Power BI and Power Query!
You’re editing the query manually to change the Speckle model URL. But you want users to select a project and model from a dropdown or slicer and then have Power BI dynamically fetch and display the results.
1. Query all projects and models from your Speckle account
You can use a GraphQL query to fetch projects and their models. In Power Query, you’d run that via a function made available by the latest data connector: speckleApiFetch , which makes a custom call to https:// SERVER_URL /graphql and turns it into a table for use in reports.
Begin a new blank Query:
let query = """
query GetUserProjectAndModels() {
activeUser {
projects(filter: $filter) {
items {
id
description
name
role
models {
items {
id
name
}
}
}
}
""",
variables = [ ],
response = speckleApiFetch( SERVER_URL , query, variables)
in
response
Rename this query ProjectAndModels
2. Create a hierarchical slicer in Power BI
- Load the projects & models table.
- Add a slicer with
Project Name and Model Name to allow users to drill down.
3. Build a dynamic URL based on user selection
SelectedModelUrl =
VAR ProjectID = SELECTEDVALUE('ProjectAndModels'[Project ID])
VAR ModelID = SELECTEDVALUE('ProjectAndModels'[Model ID])
RETURN
IF(NOT(ISBLANK(ProjectID)) && NOT(ISBLANK(ModelID)),
"https://app.speckle.systems/projects/" & ProjectID & "/models/" & ModelID,
BLANK()
)
4. Use that dynamic URL in Power Query
- In Power Query, set up a parameter and use it in your main data call:
let
selectedUrl = SelectedModelUrl , // This is your dynamic parameter
data = if selectedUrl <> "" then Speckle.GetByUrl(selectedUrl) else null
in
data
This may introduce new concepts to your Power BI arsenal, so bear with it and let me know if my memory has failed me or if something isn’t quite working or clear enough. We are also making changes to the Power BI connector so I’ll try to make sure this stays up to date.
