Parameters and Templates with Power BI Desktop

[@dapperlink]

Dude! Check it! You just put the icing on a cake I was baking! Thank you now, and later. I was able to build a Power BI solution for a team in a nice clean Template that prompts them for two files of data from two different folder locations. I parameterized dynamically loading up the list of files from each folder, changing the files names list, and using them as a query lookup to the parameter I created. All I had to do was drop parameters into my source in Advanced Editor and stitch them with &&s and it worked! SO, I hand them the template, it prompts them to look up the latest dropped files for each parameters, it all runs and Viola!, report pages are updated and their not going into Advanced Editor replacing file paths with that they had to save somewhere to drop off. Now for that second and much deserved, THANK YOU!!

[@rabsonlungu]

Excellent share! But missed on how you set up the template.

[@guavaq]

Great video I loved the "Interneet Sales" measure group!

[@pawelkuzemczak426]

Another option is to create a table with the various server details, say UAT and PROD, each having a different server address, and using that as an input in the report, such that once user selects specific environment or server, it will use that as a parameter. This avoids having to input the detail each time a report is opened. Main issue is the fact that server details would be visible to both parties, but I've seen it used in QAT comparisons across different environments.

[@LyleSpencerKirby]

Similar to Paro's question I am interested in possibly adapting this parameterization to create a dynamic query string that passes in the current user so I can then do pre-query row level security (instead of the post query RLS that has been demoed here previously). Any thoughts... I'm about to start experimenting. I am thinking I will have to use Direct Query mode, and concatenate in my parameter in the query string....

[@andreasletros7416]

LOVE IT! WIL USE IT AS A LEAD MAGNET STRATEGY

[@CheRFlowers]

Patrick - amazing and helpful of course. Is it possible to parametize something other than a SQL DB. I'm curious could your parameters connect to a selection of already published Power BI Datasets. A lot of our datasets include measures we want to carry through. So - could you create a PBI template that's has data source parameters where you could pick from a list a PBI Datasets??

[@brennyk5577]

This is amazing, you explain it so much better. Thanks.

[@GuyInACube]

Thanks for watching Brenny!

[@surbhigupta2521]

hi patrick, could you please show how to set up that intuitive/pop template when user open the powerB

[@saikumar4021]

Hey Patrick... Great video loved your style and humour

[@GuyInACube]

Thanks! 👊

[@norman.martinez7305]

Patrick, recomendations for CI/CD? Tks!!

[@karlos47]

I was looking to do the same today, by just mere luck find your video while doing another thing! thanks !!

[@abinashbehera7518]

Thanks Patrick, I was checking for Snowflake Data Source Connection, but I am not getting any options to create a parameter in the Connection window. Please suggest.

[@raghunadhamara6243]

will this work for all the data source or there any limitations

[@samikpal3583]

Patrick great solution. Amazing. Ran into same trouble, had it not been for you would have been stuck there for a while

[@pulkitagrawal2415]

Hi Patrick, in your videos, you used parameters (Server and Database) of type "Any". Will there be any obstacle while moving the report to QA or PROD Workspace? I came to know something like we should use type "Text" for the server IP otherwise it will not be possible to move the reports to other workspace. Can you please help me out in that whether that is correct or I can use type "Any" for my server IP?

[@sherwinkp]

Does this work even if the report has calculated tables?

[@hamzatariq4820]

What would be the server and database name if the i am importing the file saved on the one drive as excel workbook?

[@luxtdj]

Patrick , I have a hyphenated parameter current value ... how should use hyphen ?

[@bhushanrele4930]

Hi Patrick Thinking out of the Box for Dataset Refresh more than 8 times a day for Power BI Pro licenses. Can power bi report automatically switch datasets at a certain time of the day using Parameters? i.e. if the time of the day is between 07:00 AM to 11.59 AM then use Dataset A. between 12:00 PM to 02:59 PM then use Dataset B. between 03:00 PM to 07:00 PM then use Dataset C. This is to trick Power BI and refresh the Power BI datasets more than 8 times a day. My organisation originally requires a report to be refreshed every 10 mins but if I can give them at least every 30 mins, to begin with, it would be good. What are your thoughts?

[@kapilbhasin4945]

Great Video Patrick! Love all your videos. However i cannot find a solution if with these parameters i publish the report to Power BI report server. How can one change the parameters or apply those parameters in power bi report server ?

[@vagnermonteiro]

Hey Patrick - nice video.. but I'm trying to use this concept with Share Point Lists connection....and boom: the parameter works fine with the source step (URL). But each list has its unique ID, and this is required on the next step... so: I though if I could get this info from another internal table filtering by the first parameter... or do you have another idea how to fix the list ID based on the URL (parameter)?

[@wingsumlo7170]

Hi Patrick Thanks for your tips. I was able to discover the solutions after watching your video

[@GuyInACube]

Happy to help. Thanks for watching! 👊

[@joaquinmaverick82]

great video Patrick!! It will be usefull to me, I have a DEV enviroment and a PROD enviroment, so now I can switch between them. What happens with security? You need to store the user/password of all databases in the .pbix file?

[@Teguayco]

Learning Power BI with Will Smith is just great.

[@GuyInACube]

hahaha. Thanks for watching! 👊

[@AnilKumar-lf5tm]

Hello Patrick everything thing is perfect , we need the same data to practice it and we will get an idea about the topics what ever we learnt in this class

[@DavidPesante]

Question.... Can you set variables from other parameters... For example, rather than having the user type the server and database as two separate parameters, it would be ideal to just ask them to select from choices like... Production, Sales, EMEA, etc. and then the two parameters will be set based on this one parameter...

[@raghavendrapilli7036]

Hi Patrick it's really cool..

[@sivajan]

Can we do this with DEV and PROD environments? As long as the schema are identical; this should work, right?

[@uniQue_XL]

Great tip. Love how you presenting things. Love your humer !

[@GuyInACube]

Thank you so much!

[@ashrafalishaikh8063]

Hi Patrick , is there any way we can use parameter value in report level or page level filter

[@brookdubose]

Just made my life 1000x easier thanks!

[@busyperson4142]

Hi Patrick, is there any way to use parameters in conditional columns? for example, I want my user to select one of the value in the parameter and on the basis of that selection, new conditional column data will be displayed. So, if user select Option A from parameter then First name is displayed and if Option B is selected then Lastname is displayed.

[@olivierswerdlow2059]

Is it possible that one of the parameter will be extract from current user Active Directory account. ?

[@francoissavary3228]

I am checking for the same thing

[@alpakdeniz3989]

Love the idea! Very user friendly!

[@GuyInACube]

Thanks for watching!

[@diegoadum2701]

Patrick, super teacher!!! What about to use parameters to filter info in table 1 and based on those records to use a field to filter the data for the table 2. I'm trying to mimic the "where exists" command that I use a lot from another BI tool! Thanks!!

[@diegoadum2701]

Hey Patrick, what about to use a SQL field instead a Excel file for the data for the parameter?

[@isaacesaurg]

I am still confused with parameters. So why would i use parameters? I understand how to create them and link the paremeter to the server where the data exists and i can create a dashboard but what is the benefit of it. I am currently studying for the PL 300 certfication and the parameter topic im not able to grasp the concept of it.

[@myazigi]

Yo Patrick....good one!!! as always you guys are the best!!

[@ezpowerbi5268]

Excellent video.. really nice to have feature!

[@kallbrand]

Nice tutorial. I noticed every time I try to use Parameters for server and database it fails if I use SQL-code in the Advanced options section. I have to chose between SQL statement or parameters. Have you managed that?

[@fadydoc]

Hi Patrick, Great Video.Thank you for that.... Just have a question, I have created a report on PowerbI report server for HR which include data about different stores, however we would like each store to see only their own data not other stores. I believe report parameter could be useful however how can i control the access? Thank you in advance

[@Alan.DL7]

Row level security should do the trick in this case. They happen to have a nice course on RLS that will definitely solve or give you a good hint on how to do it.

[@harshitasingh5656]

Hi Patrick..i am trying to create a button in the pbix that should perform the action of truncating a table upon clicking it.Is there any way to do this through parameters

[@dhillon19]

I am struck at one point while testing the published template App. By Testing, i mean it is at testing phase in Release management. When I try to connect data to the database after filling default database & serverInstance, it shows me “Failed to update model parameters. Please try again later.” However it is working pretty fine when I check it by trying hands on saved template app using PowerBi desktop, it asks database & server instance & it gets connected. Am i missing something here in the case of Template App on store? I have another doubt related to this error, as, I have not provided user name password to access the data for template App, This error might be due to this problem or something else. If username password is missing how would I add it to the template parameters to make it useful for my clients who have different databases & different access details while the data schema is same for all of them. Please help me get across the issue, TIA

[@1spunch]

You are amazing.!!!

[@anuragarunedlabadkar8889]

Patrick how to use parameters created in desktop power BI in WEB power BI. I created parameters in Desktop, but after Publishing they are not visible in WEB report. Please suggest.

[@danabenbasat4168]

Hi! thank you for the video. can I add the parameters after I designed the report?

[@anthonypcheng3167]

Yes. Pretty much same as Patrick had explained in the video. For existing SQL Server data source connection: Step 1: File -> Option and Settings --> Option --> Query Editor --> Parameters (Always allow parameterization in data source and transformation dialogs) Step 2: Transform data --> Data Source Settings --> Change Source --> in the respective Server and Database dropdown boxes, change to New Parameter.

[@VarunKumar-qd5wl]

Cool pops :)

[@YYLfrank]

its also good for share to your colleagues. I'll check is there in excel or not, or i always edit pram in editing M script.

[@Parostache]

Hi! Thanks for the video. How parameters work in Power bi service? Lets say that I would like to share a report for wider audience and automatically "force" them to use their database by using parameters (and maybe restricting them seeing other servers/instances in parameter list)? Would you do it with parameters or some other way?

[@GuyInACube]

The defined parameters can't be interacted with from the Power BI Service perspective. When you get to the service, it is effectively a hard coded value.

[@enricocalzavara3977]

Hey Patrick, thank you for another amazing video!!! It does exactly what I was looking for!!! I have a couple of Parameters that I need to invoke functions to combine/expand excel sheets... You know which one? The automatically generated one [Transform file... Helper Queries... Sample File, Parameter (Sample file)...]. Then I have a parameter I have setup to choose between different projects on the opening of the pbit. Question: Is there a way to show only the choice option for this last parameter on the opening of the template and "hide" the Sample ones? Thank you so much!

[@ahmadtijani6663]

Hey Patrick, Nice video there. It's a eye opener. I'm trying to create a custom connector that pops up a web page where I can add logic for extracting that data before push onto power-bi desktop. The only way I was able to pop up a web page was using oauth but I hit a snag, I couldn't get the data onto power bi desktop. Do you think what I'm trying to do is achievable?

[@ive4862]

Any similar solution for odata source?

[@koutlaw30]

How do you supply the user with a dropdown for the server name and database name so they don't have to type it in?

[@GuyInACube]

You won't be able to do this for the end user. This is done for Power Query/Dataset level and processed at Refresh time. The end users don't get to control this.

[@emmafeng55]

Hi Patric, thanks for sharing the amazing videos about Power BI. I did learnt a lot from you. Could I ask whether you can give me a hint of how to using Power Bi desktop to show the inventory location movement? For example, item A used to be in location A in June and it moved to location B in August. I will run the report from system and upload to Power BI on monthly basis and I would like to see the transition during the selected period. Currently i can only think of using Matrix table which is a bit unsophisticated... really appreciate your help!!

[@emmastone3870]

I am new to this and have watched this video it is fantastic but how do you make the template part where the request pops up to enter the parameters? Thank you

[@GuyInACube]

Save the report as a Template. File > Save as > Save as file type = Template. You will then get a PBIT file. When you open it, it will prompt you for the parameters.

[@emmastone3870]

Thank you for your help

[@sdjensen75]

I am sorry Patrick, but I think that in real life scenarios, where you are not connecting to AWDW, rebuilding the model isn't a quick workaround. If you have like you said hundreds of queries in your model, you will probably have lots of calculated measures and relationships to recreate also. I haven't tried it, but I wonder if the trick with changing the pbix file to a zip file to be able to access the files within and then search/replace would be an solution to already build complex models?

[@SanthoshAkulaqv]

Hi is there any video that I have SQL query and I need to run that by passing parameters

[@GuyInACube]

Typically when we hear that, what you are wanting is for run time of the report based on user interaction - similar to what you can do with Paginated reports. That isn't possible today. It is coming for DirectQuery reports though - docs.microsoft.com/en-us/power-platform-release-plan/2020wave2/power-bi/end-user-editing-query-parameters Let me know if we are misunderstanding your ask.

[@mohammedhessas9788]

Hello. Thanks for this course, can we make a powerBi file with ODBC (with paramètres) witch can switch between Oracle and SQL server Databases ( the structure of databases ar similares)?? Thank you

[@khammad2001]

Hi Patric thanks for the video; I have a different question, how to protect my template and not to show the queries used to the user. I have a report which i want to resell to different users, and i need to protect my queries and protect the report from being cracked or resell. I would appreciate your help. If you have an email to write back to you

[@SravanthiMallikarjuna]

Hi, I would like to use different schema but the same server. Is it possible to do it?

[@GuyInACube]

The schema has to match for the template file.

[@walalangggg]

Hi Patrick! been enjoying learning Power Bi through your videos. May I ask, what if my data source is a spreadsheet and I need to share report with the rest of the team so they can use it without them having to edit queries one by one. Thank you.

[@Anandhamurugant]

Hi Patrick, this video looks good. But, you haven't explained how to set up the template. Would you please explain that one. Thanks

[@GuyInACube]

Thanks for watching! hmmmm sorry for the miss on that. Once the parameters are setup and your report is the way you want it, you can just do a file -> Save As and then change the save as type to Power BI Template. That's it!

[@Anandhamurugant]

@@GuyInACube,Thanks for the update Patrick

[@silas1820]

Silas Dahlgaard-Povlsen 1 second ago Hi Patrick. First of all, thanks for all the videos, much appreciated! One unrelated question, I have 3. party link to data in json format, with ticker information, that updates every 2 sec. Can I somehow fetch (refresh) this data more often than every hour? Ideally every 2 seconds, but 30 seconds or 1 minute will also be ok. Excel allows me to do this every minute. Thanks in advance!

[@AnilSharma-vi2ii]

Sir Your tutorial is excellent . I have a query that how i suppose to get the financial year like 1July 2012 to 30 Jun 2013 and 1July 2013 to 30 Jun 2014 and 1July 2014 to 30 Jun 2015 and so on till current year. This question i was asked in interview how many people have join in financial year and criteria is like above from the table date.

[@GuyInACube]

There are lots of great resources on the web regarding usages of dates in all forms (calendar year, fiscal year, ISO, etc...). You would need a date table that includes fiscal information and then create DAX measures to take advantage of the data table. There are great resources out there for time intelligence functions - www.daxpatterns.com/time-patterns/

[@aycakuvvetli7884]

Hi Patrick! I love your channel, your videos are so creative and instructive. I use power bi for a project and I want to take your advice. I have a code in excel macro to my tables taken from power bi and I need this changed values in power bi , after I run the code in excel. But I want to make it dynamic like when I update my data in power bi , my data in excel should be updated also, so I run the code then the values in excel need to be loaded again in power bi in a changed way.So how can I fix this loop? If you can help I will be appreciate. Thank you.

[@venkatareddykummitha5863]

list 5 differences between parameters vs filters? Filters vs Filter?

[@susansees]

I love the t-shirt

[@MiguelMartinPerez]

nice but no way to do that with ODBC; you must directly modify that in powerquery

[@cameronlynch9601]

Patrick, works perfectly for me with server and database, but I need the schema as well, please help. How do I do a parameter on the schema? It doesn't work when I put the parameter inside the schema. let Source = Sql.Database(Server, Database), dbo__addresses = Source{[Schema="dbo",Item="parameterTest"]}[Data] Server and Database working great. Parameter Test no good.

[@lmp9256]

Did you find Text.From - e.g. Text.From(paramaterTest) instead of 'parameterTest'? msdn.microsoft.com/en-us/query-bi/m/text-from

[@asimabdulwahab9444]

Thanks Sir!

[@GuyInACube]

+Asim Abdul Wahab thanks for watching 👍

[@manikantak8087]

Hi Patrick, How user can change the parameter value once it is published?

[@GuyInACube]

This is done under the dataset settings within the service. You need to be the owner of the dataset to change it.

[@dasrotrad]

You are hilarious! I don't want those Xs all over the place either. Wish I could have learned how to do this before I created all my Power BI reports. Thank you for such useful "how to" instructional videos Patrick. It is so refreshing to learn and be entertained at the same time. I am not connecting to a DB thorough SQLServer. So this isn't working for me. QUESTION: I need to connect to different Access.accdb or to different Excel.xlsx files. How??

[@amitshukla189]

Patrick you are funny

[@darbzlul]

nice vid , but you did not show us how to prompt the user for the parameters

[@vitodanelli]

Sorry, after the one minute of rambling, I had to move on to a video where I could learn something about Power BI. You seem more interested in hamming it up for the camera.

[@GuyInACube]

Thanks for watching Vito! And appreciate the feedback.

[@passais]

This was incredibly usefull. Thank you, exactly what I wanted to know! From my european perspective: you could tune down on the American acting a bit. To me it is as anoying as hell, I almost turned it of in the first two minutes. But hey, there is always a critic. I'm glad I sat it out. Keep up the good work!

[@chevlonmacguinstudios]

Lol I thought it was c++ templates 🤣 as if

[@GuyInACube]

hahahahaha. no. Although... :)

[@TheFuzZie]

No what you gonna do when they come for you xD kzbin.info/www/bejne/gJ24l61nbcirldkm40s

[@RakeshRKS608]

Please focus on screen instead on your face. Truly it distracts.

[@volkanbeyoglu235]

why are you so nervous? why the need to wave and gesticulate so much? it's really distracting.

[@volkanbeyoglu235]

Manus O'Donnell guess i explained why

[@volkanbeyoglu235]

I really cant keep watching. sit on your arms please, you are distracting us.

[@corinnaadkins2010]

Not distracting some people talk with their arms.