Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sslezic
Helper I
Helper I

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
Helper I
Helper I

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
Helper I
Helper I

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.