Forum Discussion

sachti's avatar
sachti
Frequent Visitor
9 years ago

Power SQL access to filter selection

Hallo,

 

short:

How can i get access to the filter in a parameter definition (Power SQL)?

 

long:

I have a dashboard with a filter.

When i select an item, I want to use the selected value in a stored procedure.

The stored procedure is working well with a given parameter entry.

How can i change the parameter automatically to the selected value?

 

thanks for helping.

 

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi sachti,

     

    What is the dashboard filter in your post? If you mean a slicer or page level/report level/visual level filter we added in report body, it is not possible to pass the selected value to stored procedure.

     

    If you want to pass a Power BI query parameter value a user specified to stored procedure, then, based on this passed value, fetch data records from databased and load into Power BI, this can be achieved. You can refer to this thread for detailed steps: parameters to SP or Sql query

     

    Best Regards,
    Yuliana Gu

    • sachti's avatar
      sachti
      Frequent Visitor
      9 years ago

      Hallo v-yulgu-msft,

       

      thank you for your help. The parameter usage is the part already working.

       

      = (Rid as text, Id as number) =>
let
    Source= Sql.Database("DATABASENAME", "DATABASENAME2", [Query="EXEC SqlStoredProcedureTable #(lf)@Rid = '" & Rid & "' ,#(lf) @Id  =  " & Number.ToText(Id) & ";"]
in
    Source

       

      this is running.

       

      The followig Picture describes it better. I want to select a jobrid and with this the stored procedure can return the measurement data for displaying in a graph.

       

       

      Thats why i try to get the selected value as parameter for the stored procedure.

      But here i use the slicer as drop down menue.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        9 years ago

        Hi sachti,

         

        As I mentioned in my original post, it is not possible to pass the selected value from slicer to stored procedure. As the available values displayed in a slicer must be fetched and returned by dataset first (in your scenario, use a stored procedure). The logic that using a slicer to filter dataset does not make sense.

         

        Best regards,
        Yuliana Gu