cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors