cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sslezic
Frequent Visitor

Dynamic M query parameter binding to a query table

I am connecting to ADX, using Kusto direct queries. My users want to see the data for a specific ID, over a resonable amount of time. They are either facing lack of data (if no data for that ID), or a deluge of data (if accessing data for all IDs).

 

My solution is to query with two parameters. One for time window and the other for ID. My time window parameter is bound to a manually gerenerated table of several time window options. This works well when my users know the ID before hand and can then gradually increase the time window until they get the satisfactory amoutn of data.

 

The problem is when my users don't know the ID and would like to "browse" to select one. My approach was to make two queries into ADX. The first would take the time window parameter and fetch all IDs available for that time window. I was then trying to bind this result to my ID parameter and offer it to the user as a slicer. The problem is that Power BI does not show me my ID parameter as an option for binding. It doesn't matter if my resulting table is DirectQuery or Import mode. If I generate a list, instead of the table, from my first query and attempt to make the ID parameter be a query driven parameter, I get a security error that I can't query from a query (or some such).

 

How to solve this issue? I need to retrieve the possible values from the server that my users can choose from. I don't want to have multi-page drill-down based report if I don't have to. I'm writing all the queries. I don't want extra security. I just want to fetch a list of options for my users to pick from, so that I can then retrieve further detailed data for them.

1 ACCEPTED SOLUTION
sslezic
Frequent Visitor

Thank you for your reply DataInsights. It turns out I was doing everything that your suggested solution in the other thread was proposing. However, I could bind my first parameter, but not my second parameter (that binds to the first returned query). I kept wracking my brains thinking it has something to do with the way my parameters were set up, types, or anything... Until the most obvious of solutions hit me. My second parameter was "loaded" into the report so that I can display the value that is being used. [/facepalm]

 

It turns out parameters must be NOT loaded into the report in order to bind them.

 

View solution in original post

3 REPLIES 3
sslezic
Frequent Visitor

Thank you for your reply DataInsights. It turns out I was doing everything that your suggested solution in the other thread was proposing. However, I could bind my first parameter, but not my second parameter (that binds to the first returned query). I kept wracking my brains thinking it has something to do with the way my parameters were set up, types, or anything... Until the most obvious of solutions hit me. My second parameter was "loaded" into the report so that I can display the value that is being used. [/facepalm]

 

It turns out parameters must be NOT loaded into the report in order to bind them.

 

@sslezic,

 

Interesting discovery! Glad you got it to work.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DataInsights
Super User
Super User

@sslezic,

 

See if this solution can be adapted to meet your requirements:

 

https://community.powerbi.com/t5/Desktop/Passing-user-selected-values-to-a-query-to-another-query/m-... 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors