Passing parameter to sql from dropdown list in powerBI (Very interesting and challenging)

[BalaVenuGopal]

Dear All,

My requiremnt is to send parameter to sql but i need drop down to choose valeu

Table Info : Assume I have tabel(Emp_Info) with columns [EmpName,Location,salary]
Requiremnt : My table have 2 crore rows
    I want to give user an option to choose location (Which shoud be dropdown list and it should auto populate from the existng  table)
    when user select any location this parameter should pass to sql database and should only data related to Selected Location.
   
Please help me this approach.

My Experiments :step 1 - Created Source 1 with sql table (select * from my Emp_Info)
    step 2 - Creted source 2 with sql select distinct Location from emp_info
    step 3 - Create new parameter(Location_param) and choose query and (choosen source 2)
    step 4 - Opened advanced editor and modified select * from my Emp_Info where location = '"&Location_param&"'
    
    But while applying changes am gettig error Please suggest solution

[Anonymous]

It turns out that this is actually possible in Power BI. I've created a walkthrough video on how to do this here.

The basic gist is to add an R visual that takes in your slicer selection as its value. There is an R package called RODBC that can run SQL code against your database. You can then pass your slicer value into the SQl query using some string interpolation or the "paste" function. Let me know how this works for you.

Thanks,

Parker

[v-chuncz-msft]

@BalaVenuGopal,

Make sure you use the Import mode and set parameter Type to Text. For DirectQuery mode, use a Slicer instead.

[BalaVenuGopal]

Hi @v-chuncz-msft

Thank you for your reply. Could you plese explain in detailed , I am planing to load data from sql to powerbi data model with filter but slicers will appear at report level correct so how can i make use of slicers here

[v-chuncz-msft]

@BalaVenuGopal,

Just drag [location] to Field.