cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Noro
New Member

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

Hi,

 

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

 

Thanks

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
Cynot
Regular Visitor

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?

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.

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
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 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
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Qiuyun Yu
Cynot
Regular Visitor

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?

lazz
Regular Visitor

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!

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.

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... Smiley Frustrated

 

Eric 

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors