Let's talk about Power BI Parameters.
Did you know that there are actually two kinds of Power BI Parameters?
One are the Power BI Query
Parameters by changing these you can change the data that is loaded in it your Power BI model. Two are the
Parameters used in Power BI reports by changing these parameters users can change what is shown on the report?
dynamically
we will cover both and
also tell you when it's best to use which approach you can access the complete playlist at the end of this video. For
this video we will focus on Power BI parameters used in the query editor
Hey, I'm I'll Avi Singh Microsoft MVP and best-selling Power BI author
And if you want to become a Power BI Pro, make sure to subscribe and click that bell
So you are notified whenever I go live to answer your Power BI questions.
So let's jump in the demo and let me show you the Power BI
Query parameters. Now the first place if you're opening Power BI desktop
You might see this in is if you click the edit queries arrow next to that and you might see edit
Parameters right here. Let me mute my cell phone. Alright, so
But you would notice that it's grayed out. It's not available because you first need to define this inside the query editor
So we're gonna launch that by clicking on this edit queries
Button, and it launches a new window in the creditor
now in here for the query editor
You would notice the manage parameters box right there and it has some options in there
You can manage parameters edit parameters or create a new parameter
We're gonna just open the manage parameters box over here
So in here is it's nice and blank and it's got nothing here, which is fine
and this is our adventure works data set and let me show you one of the reports that we have here and I
Apologize to you and Steven for you for using a pie chart here
But somehow I ended up with a pie chart so you can see here is that I have data from all the different countries
Coming in over here
And what I would like to define is a parameter
Which lets me select the specific data that comes into my model. So for that let's go over here and
So sales table is which is collecting sales data and currently as you can see this has data coming in from all different countries
So let's go ahead and define a parameter
So I'm gonna go to manage parameters and click the new button and in here I have to give it a name now. I
First try to name it country, but it wouldn't allow me to name that something which is already
Exist so country already exists. So I'm just gonna change it slightly and say let's say selected country. So I
Did that I'm gonna put a description really quick. So I specify the description now for type
You can select from different data types for this one. It's going to be text now suggested values
You can have any values list of values or build the list of values based on a query so you can query a specific
List if that's available somewhere for now. We're just gonna take that from
an excel file and
I'll just copy paste that in so I'm gonna go here and just hit ctrl we and that's my list of values
But again, I could have credited from that excel file or any other source if I wanted now
I'm often when I come over here
I hit OK
But it doesn't let me quite complete this dialog box
Because the warning is giving me is that it needs to have a current value a kind of a working value
So I'm just gonna select United States for now so that we can proceed from here
but again
This value is just from me right now so that I can I can start using it but at the end
You know, so let me show you how it's gonna actually work from there
So once you have that parameter
The way you would change that would be you would change the Edit parameters right here from power bi desktop
So you would not need to go to the query editor query editor. You only launched it to define the parameter
So now as you can see we have data from all countries
Now what happens if I go in there and I change this to let's say Australia. Well, you would find that nothing happens
In fact, even when I select the United States, I was still seeing sales by all countries
so we have one more step to complete which is to actually take the value of the
Parameter be it Australia United States
whatever is being set in the power bi desktop window and
Use that in my query itself
So for that sales is the one that's bringing in the sales from all these different countries
so what I want to go in here is go to this country field in sales and
Apply a text filter on it. So I'll say text filter
equals and now
Notice this dialog box and that I can specify a value
I can type in the United States and it would work but that's not what we're trying to do
We want this to be dynamic. We want this to change based on the parameter that's specified. So, how do we do that?
Well, if you notice that this is a drop-down and if you click on that you can change from instead of text
You can select a parameter. We could also define a new parameter right here from this window, but we already have one defined
So I'm gonna select parameter and since we only have one parameter
It just selects that one
If you had multiple parameters, then you would you would be just select the one that you want. And now we're gonna hit OK and
It was current value was Australia
so it's filtered to that and
I'm just gonna hit close and apply and I can see that it's only showing me sales for those through the country
So again now I don't have to go back to the query editor
It's defined the parameters defined
It's hooked to my query it's connected so I can just come in here say edit parameters and just select a different country
let's go back to the states hit OK and if you have to add one more button to apply the
Changes and it's gonna go back to the source and filter that data set based on the parameter that I just specified
so you can see parameters can be used in a lot of flexible ways in this case to filter the data set but
Union you could be replacing values you could be passing values back to a stored procedure
So this all may seem great and it is but there is one problem that he should know about now
there is one thing you must understand when using power bi parameters in the query editor as
Of now these parameters can only be modified using power bi desktop
There is no way for users to modify a parameter on power bi comm where they're viewing the power bi reports
Why is this important as far as Microsoft is concerned power bi desktop is meant to be used only by power bi
model authors
End-users are not really supposed to be using this tool
end-users consume the reports while a power bi comm or an on-premise option and
They're the end users currently do not get an opportunity to modify these
Parameters these query parameters if you want to learn more about the author
publish consume approach that you should be following for power bi then watch my
Complete power bi tutorial linked here and in the notes below anyone who is used to creating SSRS reports should take particular
Note of this behavior
Because it is very different from SSRS where any users can change the parameters
When consuming the web report?
so if end-users are not able to change power bi query parameters then are they of any use and are there any
Workarounds well, yes
And yes
Query parameters can be useful query parameters can be used along with power bi
Templates by model authors who are power bi desktop users
They can use it to pass along power bi desktop or PB IX models that they can reuse
By simply changing the query parameters. So if everybody involved is using power bi desktop
Query parameters work. We will show you a detailed example in a future video. We will be adding to this playlist
I should also point out that the power bi team is working towards making query parameters
Accessible why a power bi com if you would like that. Let us know in the comments below
what about workarounds one option is to simply bring in all data inside your model and then let the
end-users filter that using slicers or other filters
Or you can restrict the data that they see using row-level security
Another workaround is instead of using the built-in power bi query parameters
You can have the end-users input the parameter value using some other mechanism such as power app
SharePoint list or just an Excel spreadsheet that they update we plan to cover some of these in future videos
Let us know in the comments below which scenario is of interest to you
We will be adding more videos to this series
You can watch all release videos by clicking on the playlist here or in the description below
don't forget to join me live on my talk power bi show right here on YouTube every Friday where I would be answering all of
Your power bi questions just hit subscribe and click that bell
So you get the notifications whenever I go live and if you're new to power bi
Make sure to check out my complete power bi tutorial until next time power on my friend
Hey keep watching more videos and keep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends