How To Use Query Parameter In Power BI [2023 Update]
Vložit
- čas přidán 31. 07. 2024
- In this tutorial, Sam shows how to use the query parameter feature inside of Power BI. You can bring significant scale to your reporting by using this feature without the need for duplicated reporting. Build a model once and then use this to add additional filters to your reports.
****Video Details****
00:00 Introduction
00:24 Query parameters
01:54 Creating a parameter
02:48 Integrating a Parameter into a query
04:50 Dealing with blanks in the table
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - www.enterprisedna.co/courses/ultimate-beginners-guide-to-power-bi
FREE COURSE - Ultimate Beginners Guide To DAX - www.enterprisedna.co/courses/ultimate-beginners-guide-to-dax
FREE - Power BI Resources - www.enterprisedna.co/power-bi-resources
Enterprise DNA On-Demand - app.enterprisedna.co
Enterprise DNA Subscription - app.enterprisedna.co/pricing
Enterprise DNA Events - www.enterprisedna.co/events
#EnterpriseDNA #PowerBI #PowerBIDesktop #PowerBITutorial #PowerQuery #PowerQueryTutorial
Check out our FREE courses: bit.ly/3N00AJw
what if users wants to filter out ? as a developer you can edit this but how user will change ?
Amazing it was very helpful before interview!
Can you load a pq parameter into PBi to use in a measure, my Pbi parameter dialog box has never worked after much trying / reinstalling, so thought this might be a way round the problem.
Hi and thanks for the educational video, sorry if this is a silly question, but is there a way to feed the parameter on the report page? to give User the option of selecting that parameter? for example for selecting two dates and then return the report for them based on the combination of those dates, which doesn't work with slicers for my report.
Thanks
Hello Neda,
Below are the steps provided as follows to achieve the results based on the scenario that you've mentioned -
1). Create a parameter with a name - Start Date >>> Select type from the drop-down menu as Date >>> Mention any Date under the value section.
2). Follow the same technique as stated in step no. 1 and create another parameter with a name - End Date.
3). Go the Fact Table >>> Select the date field of the fact table >>> From the filter drop-down menu select the option of Between >>> And then, rather than putting date values explicitly, select the parameters that we've created in step no.'s 1 and 2.
4). So now, table will be filtered based on the values entered in those parameters. Click on Close & Apply and come to the report page.
5). Finally, the users of the report can edit the start and end date of the parameters by selecting the option from the drop-down menu - Edit Parameters.
Once parameters are set, click on OK and then data will be filtered and loaded accordingly.
For further queries, you can also reach out to us on our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our CZcams channel and LinkedIn group to keep posted on Power BI, Power Platform, and the Microsoft stack updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
czcams.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Yeh but no but no. This is a really horrible UX. instead they should be able to use a slider in the report to change the parameters. Anything else is an ugly hack...
what if we want to show all data in that parameter option for dashboard i.e:- including all cities name.
Thanks For this amazing trick. But I want to make it Dynamic for my End users on the Report, How can I do that ? BY using slicer, I want my end user to change date or location etc.? please make a video on it.
Exactly!!! This is what we need
Hi Sam, I have a question for you. I have parameters startdate and enddate in my report. The parameters will work fine in the desktop but when I published the report in service, the parameters will show under settings however the report will not update after changing the values of those parameters. Can you please guide me why it is not working? Appreciate your time. Thanks
Im confused, in this example there is still no difference using a paramater and just altering the filter? Can you call or change the parameter from the front end with Dax? Or is it more intended to have multiple tables using parameters? Im not sure I understand the benefot of parameters from this. Love the content though, just this one confuses me.
Hello @RakamXela
The difference between the parameter and the filters from the filter pane is that, parameters only loads the required data in the data model based on the value that has been passed in it whereas filter from the filter pane, filters the subset of the data which has been already been loaded into the data model.
And no, parameters cannot be altered via DAX since they're part of Power Query feature, but yes, you can alter them from the front-end of the Power BI Desktop by clicking onto the drop-down of "Transform Data" and then selecting the option of "Edit Parameters".
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our CZcams channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
@@EnterpriseDNAThanks, I had the same question.
Thanks. This is awesome✨ Quick question - Any idea, how can this be used on Power BI Service URL filters?
Hello @bhaskaraggarwal8971,
If your datasource is in the form of relational database such as SQL, Azure, etc then only filters can move back to the source. In case, you're using the Excel file or anything other than relational database then it's not possible to use this feature from Power BI Service.
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our CZcams channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
Thank you so much for your response. I am already your subscriber. You guys have great content. Really appreciate it!
Hi Sam,
thanks a lot for your videos. I learned a lot!
I have a case where I need to do some multi-step calculation based on user input (selections in slicers) and at the end I would like to automatically filter a table based on the calculation result.
The way I started out, the calculation result will end up in a measure. Can I get that measure into the filter criteria instead of a parameter to automatically filter the table?
If yes, how? If no, is there another way?
Hi @user-ue4ms9px1w,
It's a bit difficult to assess and recommend without looking at the data structure, model and working of the PBIX file for reference.
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our CZcams channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
Hi Sam, thanks a lot for your reply.
I found a solution in your forum --> IF( [Measure] in VALUES( Column X ); 1; 0 ) and then add that measure to filters of visual@@EnterpriseDNA
@@user-ue4ms9px1w Thanks for letting us know about your solution. I've a similar issue and looking for a solution. Basically I want RFM scores in the data set to be based upon the category like filters. RFM score will be recalculated each time a category slicer/filter is applied, in order to specialize and narrow things down. Surely it's not feasible in a big data set given that the number of calculations will be huge but it'll not be as dynamic as any other report and now that we've apply all slicers etc. option it'll be like a tiny program to perform analysis in a reasonable amount of time.
Question:
Using your example, lets say each resident is only allowed to see their own State Data. -How would a FL user open this Power BI Dashboard to only see their own state simply opening the DB Visualization?
Thank you for this walk through.
Subbed.
Hi Friendly Fire,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Then in that case/scenario, try implementing Row-Level Security and not Parameters. The intention of the technique showcased in this video is not to show data based on an individual's department/location/product but to load only the required amount of data at the back-end of the model i.e., in Power Query and then filter out the un-required records so that model remains in an optimized form. If you want to showcase the restricted data to people from same department or location then go for Row-Level Security, as suggested.
For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! You can subscribe to our CZcams channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
czcams.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
How can we pass a value to Location parameter from url of public report?
Silly question from an absolute new beginner : how is this any different from a normal filter? The steps applied here, can't it just be done with a regular filter and you end up with the same result? What am I missing?
Few questions here:
How to update query after report is hosted on PowerBI server or service. Do we need to open report in PowerBI desktop, update report and upload again?. How can we do this without opening in PowerBI desktop?
You can change the parameters if you go to the settings page of the Dataset on Power BI Service.
Hi Personal Finance,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Earlier, that was the case but not anymore. Once the parameters are created/defined in Power BI Desktop, then it can also be changed directly in Power BI Service as well. There's no need to open Power BI Desktop and go through the lengthy process of publishing it. The only time when one needs to go to the Power BI Desktop is when, if new parameter needs to be added or needs to be updated i.e., addition/deletion of a query within the same parameter. Below is the link of a documentation provided from the Microsoft's website pertaining to this topic.
For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! You can subscribe to our CZcams channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
czcams.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
learn.microsoft.com/en-us/power-bi/connect-data/service-parameters
I am missing the concrete use cases where this feature would be useful. All I have learnt from this video is a more difficult way to filter a table by a specific column value. Concrete use case examples where the usage of parameters are completely missing here
from your video, it looks like you'd always have to get back into Power Query to then select a new parameter. can't you put the list of state codes in your example in Excel where you'll select the state code and link this to the output of the query? so that you don't have to go back to Power query every time you want to change the state code
could you share please this data with us?
good, useful, etc.....
Hi @smallinnovations1864, glad you found this video helpful! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!
Instead of filtering the query in power query, is it not easier to create a slicer of state code?
I think the purpose of doing the filtering via parameters wasn't clear on this video. The parameters are officially used by some other features available on Power BI, like Deployment Pipelines and Incremental Refresh.
That said, you can manipulate how you will load your data freely, an important factor for advanced development scenarios. For example, imagine you need to protect sensitive data from prying eyes, so you create two databases. One has few thousand rows of dummy or redacted data and will be used by the Development team, the other database has millions rows of sensitive real data and will be available only for the restrict final audience.
Using parameters this becomes possible.
The possibilities are near infinite!
@@Guiburgueir4 Thank u for your detailed explanation. I am a beginner in power bi, so didn't understand its utilities.
Hi mjtr1970,
Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
When a data is filtered directly via Power Query, in that case, only that part of data is loaded in the Power BI data model and not the entire dataset. But when a data is filtered in the form of slicer, it's showing a part of data of the already loaded entire dataset in the Power BI data model.
By applying filters in Power Query, we eliminate the un-required data from getting actually loaded in the front-end of the Power BI but when a data is sliced and diced using slicers in the form of slicer, it doesn't eliminate the un-required data and rather showcases the part of the entire loaded dataset.
So by using a technique of filtering the data in Power Query keeps the data model in a optimized manner rather than loading all the data at the front-end of Power BI.
For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! You can subscribe to our CZcams channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
czcams.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Thank u for the details. Now understood the context.
Why do you want all those round-trips to the source data? Doesn't make sense .... The obvious need here, as stated by other below, is for a parameter that can be set to run across many reports and be incorporated as a filter, preferably set by a slider..