Dynamic Power BI reports using Parameters

[@davidcarballasreboredo]

it´s not just that your solutions are great, the way your videos are presented is fantastic!

[@GuyInACube]

Thanks for the feedback David! much appreciated!

[@ChickenSandwiches]

Thanks!

[@espy58]

I just started with Power BI and this helped me with a solution. Thank you very much. I did notice that parameters now have an option for Query in addition to a list or a value. But I couldn't figure out how to use it. Your solution worked great for me.

[@pabeader1941]

4 years later and you are still helping me!

[@HMZ89]

Patrick.......I just feel like giving you a hug. This was fantastic

[@unionblitz]

Patrick, this was wonderful! These explanations are very well thought out and contextual to common issues I personally run into often with Power BI.

[@sdjensen75]

To avoid the Native Query Edit permissions just go to the Security tab in Options and deselect 'Require user approval for new native database queries' - BAM.

[@GuyInACube]

woot! Thanks Soren!

[@lenac3587]

Wow, you're the man!! I had this problem and you solved it 👍

[@ammarabbasi2438]

No Such Option. Can you share screen shot

[@squallehvonllauqs1811]

@@GuyInACube BANANA...!!! ;)

[@excelscientist5041]

Unable to find this option

[@rdongre]

Being new to Power BI, had been looking for a solution for exactly same problem for days and i stumbled upon this video and solved it within few minutes! Thank you so much!!

[@GuyInACube]

That's awesome! I love hearing stuff like that. Welcome to Power BI and to a new adventure. 👊

[@TheDistractionStudio]

First time watching your video and paused the video to write this comment before I finish the rest of the video. LOVE your presentation style. I can feel the same frustration sometimes when stakeholders ask random challenging stuff but it's fun and I always accept it. Kudos to your hard work and especially the way you present it. Hollywood next stop maybe?

[@GuyInACube]

Thank you so much!

[@romankultajev3660]

Exactly what I needed. Thanks for growing my knowledge with a smile!

[@joshuaoha]

Some of this is above my head but I'm learning a lot and enjoying your videos. Thanks man!

[@GuyInACube]

We all started somewhere! Keep learning the clicks! Thanks for watching.

[@joknt438]

You are a legend! Used the same principle on a web query to pass multiple stations to a string. Love it!

[@fangedmenace]

Brilliant, this was the only comprehensible explanation of parameters I could find! Thank you!

[@AntonioSantos-ve6zv]

You're born for it! Brilliant way to present the solutions, as well!

[@PauloSilvaBarros]

Nice video Patrick! It is not a solution to the problem what i'm searching, but takes me to a mind blown about how can i create good reports.

[@GuyInACube]

+Paulo Silva Barros awesome! Glad to hear 👊

[@kimdwill]

Patrick!!! You are about to take me to school! I love the way you present. This video was very helpful. Thank you!

[@grateous7993]

You are awesome man! I actually understood this first time through, unlike the 1 on 1 tutorial I had at work.

[@thebiccountant]

Very nice vid! As you said, you can use for so many purposes: Fill in some URLs in your Excel workbook & you have a web-scraper: Create a query that takes a URL as a parameter and turn that into the function that you call instead.

[@GuyInACube]

So many possibilities :) Thanks for watching!

[@jairovera8065]

This was a phenomenal tutorial Patrick. Everything you said was clear and easy to follow. I'm currently working on a POC at work where we want to retrieve data using a REST API, which takes in 5 parameters. The users will need to be able to modify the report based on the parameters. I was able to modify your example to use a REST API and I created an excel to store different combinations of parameters. I followed along and was able to invoke the API for different combinations of parameters.

[@rowebil00]

I've used like 10 of your videos to combine all of this magic into one solution. I've subscribed. I have an API URL that shows a monthly agreement details for all of my clients. To get more details of what is covered under that agreement, I needed to enter in the agreement # into the API URL. "finance/agreements/agreementID/whatscovered" I want it so that the report has two tables, the agreements and when you select the agreement ID, the bottom table shows those details. Basically, clicking the top table column ID will set a parameter and re-run a query. I can't figure out how to do that, but this method allowed me to gather those details right now and create a table with them so that they can be referenced later.

[@mhein5589]

This was awesome, and I was able to pass two parameters in the same table. Thank you!

[@paul46722]

Absolutely love your presentation style. Really informative content and very entertaining too. Cheers.

[@malowalo]

Great work Patrick! You’re very clear on your explanations and I enjoyed seeing your videos! You have a new subscriber...

[@GuyInACube]

Awesome!!! Thank you so much for the kind words and for watching 👊

[@reneebender7789]

Thank you so much! This video just solved a problem I was having trying to filter down rows of data. I didn't realize I could just use a sql statement. Awesome!

[@GuyInACube]

woot! 👊

[@alejandrarodriguez3328]

Great video! I did something like this importing the parameter excel table, and establishing a relationship with the main table that limits the report. Probably not the most efficient way as relationships add run time. Did not use parameters. What I like about this video is how it casually introduces ways to use parameters and custom functions. Thanks!

[@lenac3587]

Inadvertently stumbled upon how to remove prefix for column headers. Was wondering about that and had to remove them in M-code the slow painful way for a dozen queries. So happy to see how to resolve this here. Had to slowdown the speed, back up and pause many times to make sure I didn't miss anything. As a constructive feedback, it's kinda distracting and makes it hard to watch when the video switches rapidly snapping us out of the computer screen and watching Patrick's expressions explaining something although it makes it more interesting and less monotonous. It's like a sudden jolt, not complaining. Nevertheless learn a lot from this invaluable training which I have bookmarked for future reference.

[@GuyInACube]

Glad it was helpful for you Lena! Appreciate the feedback as well.

[@albertjeremy3956]

Finally.. a clear usage of parameter and function.

[@georgian2133]

Its first time to use parameters in Power BI your video really helped to understand the basic thanks.

[@vpin3515able]

This is really Awesome Patrick. Kudos to your guys.

[@GLayne]

This was an amazing tutorial! Love the delivery! You got yourself a new fan!

[@rabsonlungu]

Patrick, Awesome job! You're exceptional at it. Keep sharing.

[@myazigi]

Patrick...you're awesome man!!! What a clever solution.

[@GuyInACube]

Thank you, Mauricio! And, thanks for watching.

[@myazigi]

Thanks to you Patrick, for take the time to teach us

[@wynhopkins8438]

Really good videos guys. Keep up the great work, this stuff is really useful for people. One suggested improvement would be to keep your student list in an Excel Table (Ctrl + t), to avoid accidents if someone inserts a row or column in that Excel file.

[@GuyInACube]

Great suggestion Wyn! Thanks!

[@renaudmontes7247]

Loved the form and the function, thanks Patrick!

[@tzeyongtee1579]

Great stuff!! This is exactly what I was looking for the other day!

[@GuyInACube]

Glad to hear it!

[@fredt3727]

Legend, this revolves some of the issues with not being able to use dynamic M parameters / slicer input with queries (in import mode) as you can try to pre-fetch the data using the function and give the user the "slicer" experience too (in a Rest API scenario, where the API is very restrictive in terms of data slice)

[@peterkoel8736]

You are a good teacher!

[@GuyInACube]

Thank you Peter! That really means a lot. We are learning every day to do this better. 👊

[@mikehawryluk5558]

Awesome video and clever simple solution!

[@GuyInACube]

Thanks Mike!

[@kalicharankhetwal]

i loved the way you present and make the things very easy. great

[@GuyInACube]

Thank you so much! 👊

[@tcaworf]

Nicely done Patrick!

[@GuyInACube]

Appreciate that! Thanks for watching. 👊

[@TheRehan110592]

I wish all tutorial videos just like "guys in a cube". Thanks !!!

[@thomasboles4735]

Oh and by the way Patrick the videos are great and just the right speed

[@GuyInACube]

Thanks Thomas!

[@alexjunk6216]

Great stuff, Patrick!

[@GuyInACube]

Thanks for watching Alex!

[@justme1810]

This was so helpful! Exactly what I was looking for

[@armandsn8205]

It´s my first time visiting this channel, it´s a ingenius solution to your problem, also the way the video is presented is very fun! Congrats. Have you ever try to make dynamic the report, reciving parameters for the report URL?

[@CoopmanGreg]

Patrick! you should have your own comedy show. You are really funny. I think I like your comedy more than the content, but don't get me wrong the content is really good too.

[@soy34mb]

Thank you. PATRICK, YOU ARE A GENIUS!!!

[@OzduSoleilDATA]

I'm looking forward to seeing a video on parameters.

[@GuyInACube]

Will get one on the books.

[@atteh204]

Yeah, more vids on Paramters please. They seem very dynamic feature in Power BI. How to generally utilize them more?

[@kevinjlovell]

Great example. Used this basis to connect to an API with a changing variable. Thanks for the help.

[@successfullife7689]

Unbelievable !!! Thank you very much, this is very much helping

[@ramachandravarmamandapati7476]

Great video on calling stored procedure from Power BI. Thank you very much.

[@GuyInACube]

Thanks for watching Ramachandra! 👊

[@danielperry1352]

Great Video Is there a way to use your report with parameters, but passing values directly in the URL?

[@DavidCubaDrum]

Awesome trick Patrick. Thanks!

[@GuyInACube]

Most welcome David!

[@chrisweis]

Love the Triumph dog impression at 2:33, Patrick :-)

[@CetilLee]

Very nice! Any chance of seeing what that stored procedure looks like?

[@TheKZA]

Great video, thanks. I've been wanting to know how to do this for ages.

[@GuyInACube]

Same here. Mind was blown :)

[@pouyanebrahimi835]

This video saved me hours and hours and hours :) Thanks a lot!

[@GuyInACube]

Love it! Most welcome. Thanks for watching 👊

[@pouyanebrahimi835]

@@GuyInACube Do you know how we can have two active parameters. I mean, can I have two or three different columns of different parameters with different numbers of options. Lets say, in column one I have 2 rows and in column 2, I have 5 rows. In other words, the parameter 1 has 2 options and parameter 2 has 5 options. Then I want to be able to choose different active parameters at the same time.

[@anuragks6164]

@@pouyanebrahimi835 did you find a solution for this? I have the same problem now but I am not able to solve it using Power BI

[@pouyanebrahimi835]

@@anuragks6164 Sorry to hear that. Unfortunately I could not resolve the issue by dynamic parameters. This is what I did to get around the problem. Lets say I have parameter 1 and 2 receptively with 5 and 6 options. When I am running the report, I try to load everything together. In other word, I use all options of parameter 1 and 2. After loading the data, you can easily filter it to whatever you need. BTW, I connected the stored procedures of SQL database to Power BI (follow this link: www.c-sharpcorner.com/article/execute-sql-server-stored-procedure-with-user-parameter-in-power-bi/). I hope it helps...

[@anuragks6164]

Pouyan Ebrahimi thanks a lot Pouyan. It is very tough to create that case in Power BI as i have about 15 input parameters to SP and based on the input there are so many calculations and then it displays calculated value. Should look for some other source I guess

[@inspired7606]

You are a Super Power BI Expert. Thank you.

[@joelatino3748]

This is so great. You guys are kings.

[@amitshukla189]

Hey @patrick you are super genius bro.

[@GuyInACube]

Thanks, Amit! And, thanks for watching.

[@rameshkn8203]

Nice way of presentation man!!!!! Keep it up!

[@NorbertoVeraReatigaNVR]

Muy buen trabajo! muy útil y fácil de usar... Gracias!!!

[@GuyInACube]

¡ Gracias por mirar!

[@mickaeltaieb733]

Awesome content and engaging presentation. Thank you sir!

[@estebandagobertogarciapere8457]

Awesome Video!. Thanks. Could you pass those parameters from a textbox or dropdown button and call the storedprocedure from a button?

[@deanrochester419]

Great tutorial.... problem is I have 200 plus student ids, when I do this as you show, I have to approve the native query for each student, then when I make a change to the students, I have to do the approve the running of the query for each student id again. Is there a way to have it do this approval one time?

[@nshen1200]

Like your video very much. How to use your method to have a date range as a parameter to pull the dataset, interact from the web? thx again!

[@onlydarkwar]

You have just blown my mind, I mean the potential of this tool, I just can't stop thinking about the posibilities thanks a lot!!!!

[@GuyInACube]

Awesome! 👊 Also, so much has come to Power BI since this video was published.

[@danialsamar]

Absolutely amazing. Thanks dude!

[@GuyInACube]

You're welcome!

[@MartinKuzmicz]

Great stuff - well done!

[@GuyInACube]

Thanks for watching Martin!

[@zenbabasha]

Excellent video with Great Point

[@markusjager5608]

Hi Patrick, Great video. How would you do the same report but instead of using a spreadsheet, let the user choose Students from a list on the report?

[@uniQue_XL]

wow...that's hot great out of the box solution. thanks !

[@GuyInACube]

Thanks! We thought so too!

[@snelaturi]

This is great Patrick, do you have anything that shows how to pass parameters in Direct query mode than in Import?

[@ac4613]

Loved the video! Is there an advantage to doing this using a stored proc instead of an inner join with the SQL table to the excel file.Also, I noticed your connection imports VS live query. Can you explain why (I am a powerBI newbie)THX

[@mshparber]

Awesome! Thanks a lot for the explanation! Greetings from Israel!

[@tasabeehhajahmed4084]

This is Just AWSOME!

[@GuyInACube]

Glad you liked it Tasabeeh! Thanks for watching.

[@pipriders]

thanks for this amazing video man . its solved a same problem i was struggling from last couple of days api call with with hundreds of parameters

[@mijhaelcastrociviero2848]

Nice Job! Just what I was looking for! Tks!

[@GuyInACube]

woot! Excellent! 👊

[@Stelios.Posantzis]

Great stuff! Nice presentation - very easy to follow : )

[@valentinloghin4004]

Thank you very much Patrick !!! How we should proceed if we call a function from sql and we parsing the parameter from a slicer ?

[@sau002]

Nicely explained. Can I place the Excel document in a shared location such as Azure blob storage or Sharepoint document library or some other better place you could recommend? Thank you

[@hippoborntolocalgirl]

Lovely video, great feature!

[@GuyInACube]

Thanks for watching Ben!

[@muratvezir8350]

This was made my day thank you very much

[@GuyInACube]

Always happy to hear that! Thanks for watching! 👊

[@muratvezir8350]

@@GuyInACube Dear Patrick. I have problem with this solution.If it's possible I would like to ask some questions with this solution.

[@rosswaterston]

Great video, my mind is blown!

[@GuyInACube]

That was the hope! :)

[@angelikamariae]

is all you did in this video possible while using direct query as well? I gave a try but I had some issues..

[@AnilKumar-qr9ht]

Hi Patrick, nice video. Can we do the page by page interaction with in the same report

[@netnitin]

Hi Patrick. The video was good. Can you also show how report columns can be dynamically filled as per dynamic query in parameter? say sometimes 5 column & sometimes 10 columns etc. depending on dynamic query. Thanks in advance.

[@dhanyaruth7656]

Hey Cubesters, Its Super cool.i have a question here.i want to make it as a filter ,intead of gng and refresh the things then how it work

[@laurenlavallee9592]

How can this be done when not using a stored procedure, just a regular query imported from SQL?

[@rayromych5534]

Thanks for this great video! Would be nice if we could pass parameters via variable in the power bi report link

[@kateworkman4040]

So you as the developer have to update the values in the spreadsheet everytime? How does this support the end user?

[@lgarcia67]

Is there a way to select the values from the table at run time like from a drop down? Or have an input text box to enter the values at run time? I am interested on the dynamic part but need my users to select the value of the parameters

[@siddheshmisale3904]

Amazing technique! Thanks for this!

[@Mago_Legend]

Excellent job man!!! It helped me so much. Thank you.

[@GuyInACube]

Awesome! Thanks for watching. Glad it helped.

[@jeffkeryk3550]

I was looking for this a year ago... Awesome-sauce!

[@GuyInACube]

Perhaps even #WasabiMustardSauce awesome! :)

[@JonathanCHANAL]

Man... you are just awesome ! that gives me so much possibilities ! Thank you very much ! Excellent !

[@llewellynemmanuel9907]

Great video. Great idea

[@Rokkering1]

Thanks for a great video! Is it possible to use this with DirectQuery or is this only supported by Import Mode? It would be awsome to use the functions and parameters to limit the number of rows returned from large datasets, making PowerBi an effective tool for big data!

[@arpitvijay1024]

Hi Patrick, it was great explaination and solved my doubts too, but is there any way I can pass two parameters in the single function, and my parameters are coming from two different SQL tables. Thanks

[@vinayliesure]

i have tried loading with sql table. it worked well, thank you. Good stuff