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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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