Forum Discussion

MomoJeng's avatar
MomoJeng
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

Want Kusto Query to contain user-parameters from slicers

I want the user of a Power BI report to be able to choose parameters from a drop-down menu for an Azure Data Explorer Kusto query. I have a fairly long list of possible Query Parameters that I know to be safe. I can do the following:

 

1) Go to Home -> "Enter Data" and manually enter the possible parameters in a table.
2) Go to the Power Query Editor, and turn the columns of this table into lists, which I then use to populate parameters.
3) Create slicers for these lists in the report view.
4) Use the model view to bind the values chosen in the sliders to the parameters.
5) Create a Kusto Query to Azure Data Explorer, setting it as a Direct Query, and referencing the parameters created earlier.

 

Now changing the slider selections changes the data from the Kusto call (I'm basically followed the instructions here: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#enabling-dynamic-m-query-parameters ). This is almost perfect, except that the manual list entered in step 1 is very long, and will change on a weekly basis.

 

However, if I replace step (1) with using "Get Data" and load the possible parameters from a tsv, the same process previews fine in the Power Query Editor, but when I try to save my changes, I get the error message "Direct Query ... has '2' datasource reference(s) in its expression which is not allowed."

 

Is there a way around this limitation, or an alternative way for me to do this?

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support

    Hi MomoJeng 

     

    It seems the parameter table should also come from the same data source. In this article (Use Dynamic M query parameters with Azure Data Explorer for Power BI | Microsoft Docs), it selects a table from the help cluster and transforms it into a table for slicer values. It should be helpful!

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • MomoJeng's avatar
      MomoJeng
      Icon for Microsoft Employee rankMicrosoft Employee

      Thanks Jing, that works great for me!

       

      If you have any control over the documentation, it might be worth adding the requirement that the parameter table has to come from the same data source in the "Considerations and limitations" section.