The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I am converting a SSRS report to Power-BI. In my SSRS report, I have the following SQL fragment (where RunDate is a parameter) -
CASE
WHEN (CAST(DateOfService AS date) <= @RunDate) AND (CAST(BankDate AS date) > @RunDate OR ISNULL(BankDate, '') = '') THEN Charges
ELSE 0
END AS ARCharges,
I added the following custom column to replicate the above SSRS query fragment -
#"Added Custom2" = Table.AddColumn(#"Added Custom", "ARCharges", each if Date.From([DateOfService]) <= [RunDate] and
Date.From([BankDate]) > [RunDate] or
[BankDate] = null
then [Charges] else 0),
However, I the following questions - if I want RunDate to be a parameter that can be typed-in by the end-user, how can I go about doing this Power-BI. Would the above work where RunDate is a query-parameter - would the user be able to type-in whatever value he/she needs OR do I need to use some other technique?
I am a newbie to PowerBI and I appreciate any and all help. Thank you.
Solved! Go to Solution.
Hi @diamond,
In Query Editor, you can create a Query Parameter named RunDate,
Then you can create a custom column based on the query parameter RunDate:
=if Date.From([DateOfService]) <= RunDate and
Date.From([BankDate]) > RunDate or
[BankDate] = null
then [Charges] else 0
After apply change and load to data model, we can change parameter values by clicking Edit Parameters, enter expected date value and click Apply change.
You can also download attache sample file to have a look. One thing need to note that Query Parameter doesn't work after publish the report to Power BI service, you can vote this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13820190-power-bi-online-query-pa...
Reference:
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
Best Regards,
Qiuyun Yu
Hi @diamond,
In Query Editor, you can create a Query Parameter named RunDate,
Then you can create a custom column based on the query parameter RunDate:
=if Date.From([DateOfService]) <= RunDate and
Date.From([BankDate]) > RunDate or
[BankDate] = null
then [Charges] else 0
After apply change and load to data model, we can change parameter values by clicking Edit Parameters, enter expected date value and click Apply change.
You can also download attache sample file to have a look. One thing need to note that Query Parameter doesn't work after publish the report to Power BI service, you can vote this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13820190-power-bi-online-query-pa...
Reference:
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
Best Regards,
Qiuyun Yu
Qiuyun, do you know any plans to make Power BI Service user query parameters like in Power BI Desktop?
Thanks!
Grant
Thank you @v-qiuyu-msft. That's exactly how I ended up doing. Thank you so much for your prompt reply. BTW, I was able to publish this to PowerBI service and I am able to choose the RunDate parameter (i.e. choose a date from calendar).
Thanks again for your prompt response.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
157 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |