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
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
Solved! Go to Solution.
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
Proud to be a Super User!
See the article below regarding dynamic M query parameters:
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
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
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
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.
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).
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
94 | |
88 | |
70 |
User | Count |
---|---|
166 | |
133 | |
129 | |
102 | |
98 |