Options to have report parameters similar to what we have in SSRS

[Noro]

Hi,

How to allow the user to use a report parameter similar to what we have in SSRS

Thanks

[Cynot]

I am something of a novice to PowerBI myself but you probably need to change up your approach.  What are you wanting to use parameters for?  If it is a filtering criteria then you should add slicers to your report.  Also you can use interactions to dynamically highlight / filter specific elements of your report on the fly.

Is that helpful?  If not, what do you need the parameters for?

[sbahugu2]

I may be late to reply but seems I am facing same situcation. I have a source and it can filter on date range. 

User want to see the visualization for a particular date range. But because of limitaion I have to upload all the data ( which is in millions records) and then apply slicing in loaded data.

Is there any way to apply parameter in source itself and whatever data is required for visualization load only that part using query parameter or API parameter etc.

[Noro]

Hi Cynot

Here is an example :
I have a table (or chart) that displays the sales for a year and two previous years. The user needs to insert a year as a parameter so that the table displays the values for this year and the two previous years

I hope this is clear enough
Thank you in advance

[v-qiuyu-msft · Accepted solution]

Hi Noro,

In Power BI Desktop, we are not able to specify a filter based on a DAX expression. So it’s not possible to filter report data based on dynamic values.

For this issue, I already reported it internally, will keep you updated once I get any updates.

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

[Noro]

 Hi Qiuyun,

Thank you for your answer
Someone informed me that I can create an SSRS report with parameter then pin the graph with this parameter to Power BI dashboard.
Is it possible ?

Thanks in advance

Noro

[v-qiuyu-msft]

Hi Noro,

Staring with SQL Server 2016 CTP3.0, we are able to pin report items to a Power BI dashboard. If the report has report parameters, the report parameter can’t be pinned to dashboard. We can only pin the report that is already filtered by parameter values.

Reference:

Power BI Report Server Integration (Configuration Manager)

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

[Noro]

Thanks Qiuyun Yu

[Cynot]

Noro,

In that case your query should be pulling in sales data for all years you want available, then use something like the timeline visual to let them filter on the years they specifically want to see.

https://app.powerbi.com/visuals/

Alternatively if you are tying to do something like :

SelectedYear | SelectedYear-1 | SelectedYear-2

In a table to show year over year changes, you should setup a formula that uses MDX to pull the previous 2 year's sales in their respective columns.  Are you using SSAS as your source?  If you are using SQL you could configure your query so the results look something like:

Year     |     Sales     |    PreviousYr Sales     |     2ndPreviousYear

2015           $2M                  $1.8M                               $1M

2016          $2.3M                 $2M                                $1.8M

Then with a slicer they can select an individual year and you can display year over year sales.  Is this more along the lines of what you're trying to do?

[Anonymous]

Hi there.

I am trying to switch to power BI, but without the use of parameters that I can pass to my query *before* importing/ loading the data, it is going to be impossible for me to do so.

I was reading Cynot's reply, and I was wondering: when you say we should import data for all years we want available (quoting your reply to Noro below), that would mean over 100 million rows for me... I am sitting in front of power BI watching it try to refresh graphs ever single time I try to apply filters *after* having set-up the data connection...

I am not sure if I am missing something or if there is no way to allow a use to dynamically restrict the data used to run the report, *before* running it - aka parameters in ssrs.

Thanks in advance for your help!

[Fugi]

This feature exists here:

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

[sbahugu2]

Thanks for the reply. It seems still we can set parameters from PBI desktop only. I wat users to set parameters when they see the published dashboard same as in SSRS. 

Select the paremeters and then draw the visuals on filtered source.

[EricM]

I am newbie to PowerBI too so apologies if I say something stupid...

If we can somehow embed a SSRS report in a PowerBI solution, then we can use a stored procedure as source of your report.

With that stored procedure, couldn't we

• populate a table with the parameter value
• Use this value as part of a DAX measure in a Measure only table (no lengthy underlying data, or maybe just the parameter values table)
• In Vertipaq (whether Power BI or remote SSAS tabular model), we could reprocess just that small table, which should be close to instantaneous

Would not we there achieve something similar to SSRS parameters affecting the query?

I hope I made sense... 

Eric