cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dobregon
Post Prodigy
Post Prodigy

Filter SQL query using parameters

Hi guys,

 

I want to take values from a table in my SQL server but i want to filter the query acording a parameter.

 

Imagine that i have a million of rows about daily values of customers and i want to have 2 parameters (startdate and enddate), so i want to take the dable doing something 

SELECT * FROM TABLE WHERE DATE>=STARTDATE AND DATE<ENDDATE 

 

But it seems that i can't use parameters to do that, is it possible?

Kind regards!



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
10 REPLIES 10
dobregon
Post Prodigy
Post Prodigy

I have opened again if someone can help me on this. The other solution is valid to simple queries, but now i have a query to an SP in SQL

DECLARE @dateFrom VARCHAR(10) = DATEADD(DAY,1,EOMONTH(GETDATE(),-2))
DECLARE @dateTo VARCHAR(10) = NULL

EXEC [bi].[SP_Values]
@dateFrom_input = @dateFrom
,@dateTo_input = @dateTo


As you can see i need to send values to the parameters in the SP. That i want is to create parameters in the PowerBI and call them like

DECLARE @dateFrom VARCHAR(10) = ParameterStartDate
DECLARE @dateTo VARCHAR(10) = ParameterEndDate

EXEC [bi].[SP_Values]
@dateFrom_input = @dateFrom
,@dateTo_input = @dateTo


is this possible?



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
CNENFRNL
Community Champion
Community Champion

@dobregon , you may want to refer to this article on passing parameters to sql queries.

https://blog.crossjoin.co.uk/2016/12/11/passing-parameters-to-sql-queries-with-value-nativequery-in-...

edhans
Super User
Super User

It depends on how you are setting those values. I use queries as a scalar value for this. So I might have a query that simply evaluates to = #date(2021,3,1), then I have a simple query like this:

let
    Source = Sql.Databases("localhost"),
    ContosoDW = Source{[Name="ContosoDW"]}[Data],
    DaxBook_Sales = ContosoDW{[Schema="DaxBook",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(DaxBook_Sales, each ([Order Date] >= varStartDate))
in
    #"Filtered Rows"

Power Query will then fold that and send this SQL statement to the server for processing:

select [_].[OnlineSalesKey],
    [_].[StoreKey],
    [_].[ProductKey],
    [_].[PromotionKey],
    [_].[CurrencyKey],
    [_].[CustomerKey],
    [_].[OrderDateKey],
    [_].[DueDateKey],
    [_].[DeliveryDateKey],
    [_].[Order Date],
    [_].[Due Date],
    [_].[Delivery Date],
    [_].[Order Number],
    [_].[Order Line Number],
    [_].[Quantity],
    [_].[Unit Price],
    [_].[Unit Discount],
    [_].[Unit Cost],
    [_].[Net Price]
from [DaxBook].[Sales] as [_]
where [_].[Order Date] >= convert(datetime2, '2021-03-01 00:00:00')

 

varStartDate (you can call it whatever you want) can be hard coded, dynamic based on today's date - =DateTime.Date(DateTime.LocalNow()), or any other date logic. It can also be based on values in another query, so 

= List.Min(
    List.Buffer(SomeQuery[Order Date])
   )

Would return the earliest date from the order date field of SomeQuery. You could further manipulate that with Date.StartOfYear, etc.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks @edhans and @CNENFRNL  for your replies. 

I know how to create parameters int he SQL or filter in the table, but what i want is to create 2 parameters in the PowerBI (startdate and enddate) and then filter the SQL query / Power Query related to that parameters

dobregon_0-1616524788854.png

 


My idea (if it is possible) is to create both paremeters with short dates, upload the PowerBI to the service and then change the startdate to 2020-01-01 and then refresh the dataset. This is what I'm asking, the possibility to read that type of powerbi parameters in the PowerQuery.

is it possible?

Regards!



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

My point @dobregon is you are thinking SQL parameters and Power BI parameters are the same thing. They are not. My example above shows you code how to pass the start/end date variables to your data. Your StartDate could be something like this:

 

= Date.StartOfYear(Date.AddYears(DateTime.Date(DateTime.LocalNow()),-3))

 

Today that will generate Jan 1, 2018, and will cause your SQL query see it as :

 

where [_].[Order Date] >= convert(datetime2, '2018-01-01 00:00:00'

 

when PQ passes the date if you use it like I showed above.

 

You can use the dates as parameters like you've shown, but they will not be dynamic. You have to go to the service to change them. Certianly possible, but I usually reserve those parameters for database and server names. My start/end dates need to adjust themselves over time.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

yes @edhans , i know they are different. 

I dont want a query to calculate the datestart, my datestart changes when depends on what i need in the next refresh in the service. So I'm asking if it is possible to conect the PowerBI parameter to the PowerQuery in order to tupload the report to the server, and for example the next month i think that the startdate should be 2019-01-01 and i only change the paremeter in the service and then the next refresh will use that parameter to take the info in the table.



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Yes. Parameters you put in Power Query will show up in the service here for you to manually change.

edhans_0-1616526025092.png

 

You would still incorporate those parameters into your query as I showed above. You'd just reverence the parameter vs a query with a date.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

yes, but how can i do the query?

For example, imagine that the parameter in PowerBI is called StartDatePeriod with a value 2020-01-01 and the query that i have to the SQL is the typical

SELECT * FROM dbo.Table

and i want to include the parameter doing something like  

SELECT * FROM dbo.Table where Date>= @StartDatePeriod

I have tried this and it is not working

My idea is then to have something like this in the PowerService

dobregon_0-1616576034751.png


and when i want change to and in the next refresh (in the future) the system will take from 2022

dobregon_1-1616576051319.png

 



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

There are only two ways I know of @dobregon 

  1. Don't use a SQL Query but rather let Power Query create the query by query folding, which is what I've shown above.
  2. Read Chris's blog that @CNENFRNL linked to about passing parameters to SQL queries.

I personnally prefer option 1 as it is 100% dependent on Power BI and reduces coding time - I only have M to deal with, not M and SQL, and subsequent changes in logic won't be impacted.

 

But I fully understand some are more comfortable with SQL, so the article linked to above would be the way to go.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks @edhans 

 

In reality, that i didnt understand is what simply was to put the same name than the parameter in the powerquery and this will filter the query... 

i just find the new video from  Patrick that it is very visual to see what i'm looking for and for future visitors to this topic.


Thanks @edhans  and @CNENFRNL  for your support!





Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors