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
dobregon
Impactful Individual
Impactful Individual

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)
1 ACCEPTED SOLUTION
dobregon
Impactful Individual
Impactful Individual

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)

View solution in original post

13 REPLIES 13
Do5779
Helper II
Helper II

Have you @dobregon or someone else found a solution yet?

dobregon
Impactful Individual
Impactful Individual

sorry i dindt accept the solution. it is on the video.

thanks!



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

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-...


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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
dobregon
Impactful Individual
Impactful Individual

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
dobregon
Impactful Individual
Impactful Individual

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
dobregon
Impactful Individual
Impactful Individual

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
dobregon
Impactful Individual
Impactful Individual

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)

Thanks for the parameters (Dates) this is helpful.  Does this really impact the refresh load?  As that is my expectation.  So if my general ledder SQL table has 1,500,000,000 rows I and 10 years of GL data.  I don't want it all and just really need Last three months of the pervious year and the current year.  So two sets of parameters with from and to date each.  Or is there a more lean way to parameterize this. With two datasets for GL data previous year last three months and current year; and then I append one to ther other for a sinble dataset and have one not refresh.  Your thoughts. Please

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.

Top Solution Authors
Top Kudoed Authors