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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
dwglot
Regular Visitor

PowerBI - Ability to Prompt User for Date retrieved table, Date becomes query variable

Greeting, trying to figure out a way to prompt the PowerBI Report user to select a date from a table, and then the date selected becomes the query variable value.

Here is my PowerBI Query, need the FromDate and ToDate to be set by the User selecting;

= Sql.Database("Server", "Database", [Query="Declare
@FromDate as Date = '" & FromDate & "',
@ToDate as Date = '" & ToDate & "'

SELECT
[ID]
,[CompanyCode]
,[Report_Affiliate_Code]
,[NAV_Affiliate_Code_List]
,[NAV_BankAccount_List]
,[Description]
,dbo.fn_ClearedPaymentsCountBAI(@FromDate,@ToDate, [XXX_Affiliate_Code_List],[CompanyCode]) as PaymentCount
,dbo.fn_ClearedPaymentsAmountBAI(@FromDate,@ToDate, [XXX_Affiliate_Code_List],[CompanyCode]) as PaymentAmount
FROM [dbo].[AffiliateReporting]
WHERE dbo.fn_ClearedPaymentsCountBAI(@FromDate,@ToDate, [XXX_Affiliate_Code_List],[CompanyCode]) <> 0
ORDER BY [Sort Order]
OPTION (RECOMPILE)"])

 

Any assistance is greatly appreciated,

dwglot

1 ACCEPTED SOLUTION

@dwglot,

 

Here's an example using a custom SQL function. The data type of the parameters is Date, and is converted to text in M to make the concatenation work. Upon execution in SQL, the data type of the parameters is converted to Date.

 

let
    StartDate = Text.From(prmStartDate),
    EndDate = Text.From(prmEndDate),
    Source = Sql.Database("localhost", "Sandbox", [Query="select Sandbox.dbo.CountDaysInDateRange('" & StartDate & "', '" & EndDate & "')", CreateNavigationProperties=false])
in
    Source

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
DataInsights
Super User
Super User

@dwglot,

 

See the article below regarding dynamic M query parameters:

 

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you DataInsights,

    Not quite what is required, I have two Date parameters (FromDate and ToDate), which the User will select from. The date selected then becomes a value needed for a function within the query. Basically, placing the date values selected into the function variables.

let
selectFromDates = Type.Is(Value.Type(FromDateParameter), List.Type),
selectToDates = Type.Is(Value.Type(ToDateParameter), List.Type),

Query="
SELECT
[ID]
,[CompanyCode]
,[Report_Affiliate_Code]
,[NAV_Affiliate_Code_List]
,[NAV_BankAccount_List]
,[Description]
,dbo.fn_ClearedPaymentsCountBAI(("selectFromDates","selectToDates"),....

 

Any assistance is greatly appreciated.

Thank you,

dwglot

@dwglot,

 

Here's an example using a custom SQL function. The data type of the parameters is Date, and is converted to text in M to make the concatenation work. Upon execution in SQL, the data type of the parameters is converted to Date.

 

let
    StartDate = Text.From(prmStartDate),
    EndDate = Text.From(prmEndDate),
    Source = Sql.Database("localhost", "Sandbox", [Query="select Sandbox.dbo.CountDaysInDateRange('" & StartDate & "', '" & EndDate & "')", CreateNavigationProperties=false])
in
    Source

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




The solution did not work. While I am able to change the dates while editted the parameters, the users need to set the dates from the report view, acting similar that a slicer.

@dwglot,

 

It sounds as if you want to use dynamic M query parameters (I sent a link in my original reply). I was unable to adapt the solution to use dynamic M query parameters due to the query using Import mode (Import mode was automatically chosen with no chance to override it). If the query can be converted to DirectQuery, it should work. You would need to create a date table in your database and create two DirectQuery tables that point to the database date table. This would allow you to bind the parameters (data types must match).





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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