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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power BI parameter for SQL Query

Hi Community,

Do you know if there is a way that I could use Paramter in Power BI for my SQL query?

I have a simple SQL query like this:

SELECT *
FROM table1
WHERE year = 2018

I would like replace 2018 with Parameter that a user could enter. I am using Import mode in Power BI and custom SQL for my sources. And feedbacks or thoughts would be appreciated

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi NickNg278,

 

Yes, you can call query parameter when you implement SQL statement in power bi, you can also refer to this blog:http://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-paramete....

 

Regards,

Jimmy Tao

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

Hi NickNg278,

 

Yes, you can call query parameter when you implement SQL statement in power bi, you can also refer to this blog:http://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-paramete....

 

Regards,

Jimmy Tao

Anonymous
Not applicable

Hello, 

 

Somebody have a solution ? 

Is actually a HUUUUUUUUGE Problem for my company and block all development on powerbi. 

How this version can be in production ??? 

 

Thanks. 

I'm probably a bit late but maybe this can help someone else.

 

First create a stored proceedure in your SQL server that has a start date (can be start + end date) but for our company we just want a start date:

Here is some simple SQl code to show this - outlined in bold the iimportant bits (im not here to teach sql lol)

USE [MyMainDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_Midnight_Census]
(
@vStartDate Date
)
AS
BEGIN
SET NOCOUNT ON;

-- Step 1 is to create a temporary table to store our date range…
create table #dateTable (thedate datetime)

--Step 2 is create some variables for the start and end date.
declare @dtEnd date, @hour int, @days int
set @days = DATEDIFF(DAY, @vStartDate, CURRENT_TIMESTAMP)
set @dtEnd = dateadd(day, @days, @vStartDate)
set @hour = 00

--Step 3 is to insert our dates into the temporary table… we do this with a bit of SQL Server magic that selects sequential numbers from a master table and then adds the same number of days to our base date.
--I have also added 00 hours to the date (@hours) because for this dataset we want midnight patients. For midday etc @hour would be set to 12
insert into #dateTable
select DATEADD(hour, @hour, CONVERT(datetime, dateadd(day, number, @vStartDate))) as 'thedate'
from
(select number from master.dbo.spt_values
where [type] = 'P'
) n
where dateadd(day, number, @vStartDate) < @dtEnd


--Final step is to join this date table with the MyDATA transfer table. 
--NOTE: dischg_dtm = null so my join is in actual fact slightly wrong
select d.thedate, t.ward, count(*) as occupancy
from [server\HOSP2020].[MyDATA ].[dbo].[tablename] t
join #dateTable d on d.thedate between ward_admit and ward_disch
where t.ward_id like 'FP%' 
group by d.thedate, t.ward
order by d.thedate, t.ward

drop table #datetable

END

* Now I want to create my Power BI query/new paramter to handle this SQL connection

  • Open Power BI
  • Open the Document to link the data to
  • Click on: Get data > Blank Query
  • Click on Manage Parameters > New Parameter
  • Under Name type "Somethign Descriptive"
  • Type server\name into “Current Value”
  • Select – OK
  • Select Advanced Properties
  • Replace anything here with

    let 

    SQLSource = (vStartDate as date) => 

    let 

    Source = Sql.Database("server\Name", "ClinEpiReporting", [Query="exec sp_Midnight_Census '"& Date.ToText(vStartDate) & "' #(lf)#(lf)#(lf) #(lf)"]) 

    in 

    Source 

    in 

    SQLSource

  • Click Done
  • Select Query and enter a start date and then click “Invoke”.
  • It may show a warning
  • Click “Edit Permission”
  • It may show a warning to run the query in Native Mode. Click “Run”.
  • It will load all the data.
  • Click “Close and Apply”.
  • This should get you going, we found it relatively easy to create a stored proceedure using google. Everything outlined here is pretty much how we start off - of course our stored proceedure has a lot more in it and the one here is a simngle table example, not something useful to you, but ive outlined in bold text the main parts which let you make it a start date paramter, which I've found really useful in Power BI. Hope it helps.
Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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