Forum Discussion
SSRS to Power BI Report
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.
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 0After 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-parameters
Reference:
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/Best Regards,
Qiuyun Yu
3 Replies
v-qiuyu-msftCommunity Support
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 0After 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-parameters
Reference:
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/Best Regards,
Qiuyun YuThank 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.
Qiuyun, do you know any plans to make Power BI Service user query parameters like in Power BI Desktop?
Thanks!
Grant
