Let's see how we can use Power BI Parameters to create dynamic
Reports in Power BI this is part of a series on Power BI Parameters
You can access the complete series by
Clicking in the corner or in the description below now in the part 1 we had covered Power BI
Query parameters now in this part we're gonna focus on the Power BI report parameters.
So I want to show you step-by-step how you can use them in Power BIi to create dynamic reports
But in the end we'll also compare the two approaches and decide which one is the best for you.
I would also like to hear from you as to how you are using or plan to use Power BI parameters
so leave a comment and let me know. My name is avi Singh
I'm a Microsoft MVP and a best-selling Power BI author
And if you would like to become a Power BI Pro
make sure to click subscribe and hit that bell so you are notified whenever I go live to answer your Power BI questions.
So we had covered query parameters in part 1 of the video
But I'm gonna give you a really quick recap query parameters are set in the query editor
But once set they can be changed just from edit queries edit parameters
window here
And in in this example how we had used was we had used this to select the data that is loaded into my report
So by selecting United States, I'm only loading the data for United States
now, let's switch over and look at the report parameters report parameters can be
Used the way you saw query parameters being used primarily to filter the report data set
But it also has additional uses for what-if or a selection office scenario and I'll show you all of those
So let's look at the first one
Here we have
similar to how the query parameters were
selecting the country the data that was shown you can bring all the data in and simply show it as a
slicer on here and the user themselves can select which country the data they want to
Filter what they're looking at now
Of course
You can also add this as a base level or the board level filter instead of adding it as a slicer here
Now if you do not want to expose all your data in this way if there are some security or sensitivity around it
You can use row-level security
So that users from the United States would only see data from the United States
Automatically and we'll cover that possibly in a future video for now. Let's stay with the parameters report parameters
So this is one use but let me show you another use of report parameters
Which is the what-if or scenario selection now, what I'm going to take you is our finance dashboard now
this is one which we have built step by step in a very
Detailed hour-long webinar that I did with Microsoft and we're gonna link that in
You know up in the corner or down in the description as well and you can watch that in detail
But this was the end result of that and I wanna share that with you
So here you notice here that this is our parameter
so this is the dial that the user can select and it's gonna
Dynamically change the report and here they select the scenario for which they want to see more details
So right now I'm gonna orient you here. So notice this number eleven five zero four it matches over here
So what this dial is doing is it's connecting this one
With the parameter selected, so when you select the year-to-date it shows you the detail for the year-to-date numbers
but when you change that parameter one when the user selects quarter today notice that now
This number matches this and it's showing you the quarter to date details
and of course when you select current it gives you the details for the current month so you can see how a
Parameter can dynamically change what's being shown on the report many users along this line
So don't get stuck with the specific of that
Just get the idea that you can have a dial a parameter on your report
Which can dynamically pretty much change anything that is being displayed on your report for this video
We're gonna develop a simpler version of that. So this is a
projection
Scenario and here our dial is the growth rate
So here we have traveled back in time and we are at the beginning of the year
2017 and we have our prior year data, which is 2016 available to us
and then we are using that to project the future the
2017
Numbers that are going to be and/or dial the parameter is the growth rate
So right now it's at 0% and we're saying yep zero it's gonna stay flat
But then I can user can change this dial and say what happens if we grow by two
Percent what happens if you grew about 5 percent or 10 percent you can see ten percent
It's easy to validate or actual priori. It was 16 million. So with 10 percent growth will grow by
1.6 million and you can see that's been added to that there so that all dial is there available to you?
But the best part and this is what I love about power bi is that once you build something like this?
You can slice and dice it any which way you want
So if I want now to see this projection only for United States or only for Germany is just a click away
or I can go just to the United States and say hey, show me only for the
Accessories business and the same logic you don't have to touch anything. It's still gonna be applied on the display on the report
So let's go ahead and build this out step by step
So just so you can follow along and get every little step that's involved here
and by the way
You can download this file
And any other file you see on my videos here by going to dub dub dub?
Learn power bi comm slash download and that includes the finance dashboard that I had shown to you earlier
So go ahead and grab that file so you can follow along in this video if you like or you can practice it later on
So here I have again the year. I have a slicer and it is selected
2017 and I've already defined a measure. So 2017 is a year if you want to calculate the projection for
2016 is the year that's just completed and to get the last year's number
I've already defined one formula
Using the amazing time intelligence available
In power bi so this is actual prior year and you can see it's it's a very simple format calculate
Actual and then it uses one of the time intelligence functions to give me the value for last year
So same period last year so I have that one available here
let's add a
Projected number shall we so you know, what we're gonna do is we have actuals over here
I'm going to add a new measure and
Let's go over here
And we'll say projected and I know I'm going to create the projection Bay based on the actual prior year
So let's just say prior year and for now, let's just say x one
I mean you don't have a growth rate dial quite there yet. But so we're gonna leave a placeholder for that here
I'm gonna change the format for that and let's just add it here. And right now it's not gonna do anything
It's just showing us the same number
So the first thing we need to do is to build that dial and to build a dial
I need to provide those numbers to power bi so for that I'm gonna go back to home and enter data
Now you can connect this to a separate file. But this one for a simple scenario
I'm just gonna copy paste this in from an Excel list that I have here
so I'm gonna I
hit enter data earlier, and now I can just steal in here and I can say ctrl V and I just paste that here and
I'm just going to rename the table and hit load
So what this is going to do is it's gonna make that that dial the parameter available to us
It's not gonna be functional to begin with but that's you know
Keep watching to the end to see how it's hooked up all mmm
so my growth rate is right here and I what I'm going to do is I'm going to add that here change that to a
Slicer I'm gonna change that to a list slicer. Oh, I shouldn't format that as a percentage as well
So I'm gonna change the formatting do percentage
Let's let's see how this works. Perfect
so now it's a percentage and
I do not want any decimal places and what I'm gonna do is change this to a
Horizontal a slicer that's the look that I'm going for for this video, and I'm also going to add
some
Framing around these items. So I'll go to items and I'll say if we're outline frame those. Okay, that's good enough
And so now we have the dial available. So let's see how it works. Shall we? Well 0 percent
that's understandable about 5 percent 10 percent you notice that nothing really changes and I'm also going to add the
Projected big projected number here, but it's not really changing anything because it's not hooked up yet
So remember when we had defined the projected measure we had just left kind of a placeholder there and this is where we're gonna substitute
Our selected value for the growth rate. So for that I love doing things step by step
So we're gonna do a mini measure just to grab which what is the growth rate that has been selected in that dial?
so I'm just gonna go to that and
Say give me a selected growth
Rate and I'll just say selected value
growth rate and
That's it
And I'm going to change that to a percentage no decimal places and I'll just put it here just to see if it's working
I expect it to and
Perfect. So 10% so you notice that if I change this that at least this measure is changing now
This measure is still not changing. But that's the one we're gonna fix next
So I'll remove the selected growth rate from the display over here. I'll go back to projected and instead of one
I'll say hey if it's selected as one point
10% growth I'll say one plus the
Selected growth rate and let's see how it does
Now perfect and and again we can see, you know, a kind of a ten percent growth rate and a minus two percent
So it's changing. Let's add year-over-year
Projected growth change as well. So year-over-year
Projected
Growth in like actual dollars and we'll just say projected. Oops
- actual
That's it let's bring it back to ten percent and here I'm going to add
At the ear over here projected in this as well
And I'm going to add this in my detail as well. And I just realized that I do need to format it again. Oops
I made a mistake there. So instead of projecting - actually should have been
prior ear
Difference so let's do that. And now it's working right so you can see at ten percent growth, which I had selected over there
We're growing by about
1.6 million, so that's 16 4 is
You know so that that seems about right so the last thing I added in this report is
cumulative so year-to-date totals just to get like a visual indication like a fee if you're growing at 20% you can see
If you're flat at 0% and of course the two lines superimpose
But we you know
You can see how from minus twenty percent to twenty percent and you can see kind of month by month
How much it's pretty much the same information but in cumulated format and you can see at the year-end
Priority date was sixteen point four million, but
Projected the year over twenty percent growth is 19.7
You can grab this file and my other downloads at
Learn barbie are calm slash download and we're also going to put that link in the description below
Now, let's go back and compare the query versus the report parameter
So you if you watched both parts of this series
Then you have seen the query parameters in play and you've seen the report parameters in play now query
parameters the primary use of this is to filter the incoming data set now you can theoretically use this for what if or
Scenario selection, but it's extremely rare and I've never really seen it being used that way. Although it is possible
Report parameters can be used for filtering as well as what-if scenario
Whatare for scenario selection there and they're extremely flexible when it comes to that
Another limitation around query parameters is that they're only they can only be changed when you're in the power bi desktop
Which is really the authoring tool once a report is published onto the service Barbiero more power bi reports or on-premise
you and the users currently are not able to change the query parameters all
Whereas the report parameters are always visible in power bi desktop or power bi comm and can be freely changed by any end user
Because of those reasons right now my heavy favorite are the report parameters
But I would love to hear from you how you are using parameters and which one is your favorite or how you plan to use?
them so leave a comment and let me know make sure to watch the complete power bi query parameters series by
Following the links in the corner and down below now
We have covered query parameters and parameters
But more videos on this are coming soon
Possibly will cover topics around rola with security using external parameters and more if you have a specific topic in mind
Please leave a comment and let me know what would you like me to cover if you're new to power bi make sure to check
My step-by-step power bi tutorial and remember to subscribe and join me live on my Friday talk power bi show power on my friend
Hey keep watching more videos and deep 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