Dynamic Power BI reports using Parameters
Vložit
- čas přidán 23. 05. 2017
- In this video, Patrick shows you how you can use a parameter, within a Power BI report, to dynamically change the data in a report. This uses M Functions within Power Query and a second source that has the key values to pull. Then using a gateway to enable refresh.
LET'S CONNECT!
Guy in a Cube
-- guyinacube.com
-- / guyinacube
-- / guyinacube
-- Snapchat - guyinacube
-- / guyinacube
**Gear**
Check out my Tools page - guyinacube.com/tools/ - Věda a technologie
it´s not just that your solutions are great, the way your videos are presented is fantastic!
Thanks for the feedback David! much appreciated!
Patrick, this was wonderful! These explanations are very well thought out and contextual to common issues I personally run into often with Power BI.
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.
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.
woot! Thanks Soren!
Wow, you're the man!! I had this problem and you solved it 👍
No Such Option. Can you share screen shot
@@GuyInACube BANANA...!!! ;)
Unable to find this option
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.
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!
Patrick.......I just feel like giving you a hug. This was fantastic
You're born for it! Brilliant way to present the solutions, as well!
Patrick!!! You are about to take me to school! I love the way you present. This video was very helpful. Thank you!
This was an amazing tutorial! Love the delivery! You got yourself a new fan!
Some of this is above my head but I'm learning a lot and enjoying your videos. Thanks man!
We all started somewhere! Keep learning the clicks! Thanks for watching.
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!!
That's awesome! I love hearing stuff like that. Welcome to Power BI and to a new adventure. 👊
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?
Thank you so much!
Brilliant, this was the only comprehensible explanation of parameters I could find! Thank you!
Great work Patrick! You’re very clear on your explanations and I enjoyed seeing your videos! You have a new subscriber...
Awesome!!! Thank you so much for the kind words and for watching 👊
4 years later and you are still helping me!
Patrick, Awesome job! You're exceptional at it. Keep sharing.
Absolutely love your presentation style. Really informative content and very entertaining too. Cheers.
You are awesome man! I actually understood this first time through, unlike the 1 on 1 tutorial I had at work.
You are a legend! Used the same principle on a web query to pass multiple stations to a string. Love it!
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.
So many possibilities :) Thanks for watching!
Its first time to use parameters in Power BI your video really helped to understand the basic thanks.
Loved the form and the function, thanks Patrick!
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.
+Paulo Silva Barros awesome! Glad to hear 👊
This is really Awesome Patrick.
Kudos to your guys.
Exactly what I needed. Thanks for growing my knowledge with a smile!
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!
woot! 👊
Awesome content and engaging presentation. Thank you sir!
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
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.
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.
Great suggestion Wyn! Thanks!
Great example. Used this basis to connect to an API with a changing variable. Thanks for the help.
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?
Great stuff! Nice presentation - very easy to follow : )
This was so helpful! Exactly what I was looking for
This was awesome, and I was able to pass two parameters in the same table. Thank you!
Great video on calling stored procedure from Power BI. Thank you very much.
Thanks for watching Ramachandra! 👊
Great stuff!! This is exactly what I was looking for the other day!
Glad to hear it!
i loved the way you present and make the things very easy. great
Thank you so much! 👊
Great video! You've helped me a lot. Quick question, if I am making a report that has model numbers and their related data, how could I go about making the report appear with no data until the user uses the filter function to show data for model numbers they choose to filter?
i have tried loading with sql table. it worked well, thank you. Good stuff
Amazing technique! Thanks for this!
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.
Glad it was helpful for you Lena! Appreciate the feedback as well.
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)
Nice way of presentation man!!!!! Keep it up!
Awesome video and clever simple solution!
Thanks Mike!
Great video, thanks. I've been wanting to know how to do this for ages.
Same here. Mind was blown :)
Excellent job man!!!
It helped me so much.
Thank you.
Awesome! Thanks for watching. Glad it helped.
Love the Triumph dog impression at 2:33, Patrick :-)
Man... you are just awesome ! that gives me so much possibilities ! Thank you very much ! Excellent !
Nicely done Patrick!
Appreciate that! Thanks for watching. 👊
This is so great. You guys are kings.
Thank you.
PATRICK, YOU ARE A GENIUS!!!
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!!!!
Awesome! 👊 Also, so much has come to Power BI since this video was published.
I wish all tutorial videos just like "guys in a cube". Thanks !!!
I was looking for this a year ago... Awesome-sauce!
Perhaps even #WasabiMustardSauce awesome! :)
Hey Cubesters, great video! I'm in awe of your creativity in coming up with solutions that automatize everything so neatly and concise.
If I may, I have an issue of my own which I simply cannot find the solution to.
I pass a custom query in advanced editor to a MYSQL server and would like to make it dynamic by passing a parameter inside the query. However, I'm unaware of how I'm supposed to that inside the ""WHERE"" part of the query. For example, I've created a parameter (LUNA) which resembles the month number and would like to pass it to my query in this section ** WHERE MONTH(data)
Nice Job! Just what I was looking for! Tks!
woot! Excellent! 👊
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
Great work, thanks man!!
Great stuff, Patrick!
Thanks for watching Alex!
Excellent video with Great Point
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!
You are a Super Power BI Expert. Thank you.
Absolutely amazing. Thanks dude!
You're welcome!
Awesome trick Patrick. Thanks!
Most welcome David!
Lovely video, great feature!
Thanks for watching Ben!
Unbelievable !!! Thank you very much, this is very much helping
Hi Patrick, this video was really cool, helped me a lot thanks. I hope you can help me out on how to set query parameters from javascript for PowerBI embedded like we do to set report/visual/page level filters, I dont know whether its supported or not.
wow...that's hot
great out of the box solution.
thanks !
Thanks! We thought so too!
Great video. Great idea
Oh and by the way Patrick the videos are great and just the right speed
Thanks Thomas!
brilliant brilliant presentation awesome....
Wow! It is aaaaawesome. Many Thanks !! Great job and please keep it up buddy :)
Thanks so much!
Finally.. a clear usage of parameter and function.
Thank you.. was looking for this!
Ax
This is great Patrick, do you have anything that shows how to pass parameters in Direct query mode than in Import?
Awesome! Thanks a lot for the explanation! Greetings from Israel!
You are a king!!
I just started learning power hi
And this video is awesome 😃
Awesome video, it solves a big part of my project request. On top of parameters, Patrick, my user wanted to supply a date to be inserted to the report as a new column, like something they do in excel. Is it doable in Power BI and if so, how would you do that?
Patrick...you're awesome man!!! What a clever solution.
Thank you, Mauricio! And, thanks for watching.
Thanks to you Patrick, for take the time to teach us
Great stuff - well done!
Thanks for watching Martin!
Thank you very much Patrick !!! How we should proceed if we call a function from sql and we parsing the parameter from a slicer ?
Hey @patrick you are super genius bro.
Thanks, Amit! And, thanks for watching.
Awesome Video!. Thanks. Could you pass those parameters from a textbox or dropdown button and call the storedprocedure from a button?
Wow, great videos!
Thanks for this great video!
Would be nice if we could pass parameters via variable in the power bi report link
Thank you very much by this lesson!
2 Thumbs Up! I have several use cases to apply this to. Thanks
Hi Patrick..Superb video.. I have a requirement in which I need your suggestion. I need to dynamically pass the parameters so that the report automatically filter out with respect to a ChatBOT prediction. Is it possible?
Question: Can PowerBI be embedded into a Unity 3d application? And/Or can a Unity 3d application be embedded into a PowerBI tile/report?
Sorry the question was a little off topic. I'm just having trouble finding out if this is possible and you guys are are PowerBI gurus. By the way, love the videos. Very clear, concise, and engaging.
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?
Very nice explained! I will adapt this technique to use values from another query and not Excel :)
Thanks, Patrick!
Yup, it should work with anything. Not just an excel sheet.
Muy buen trabajo! muy útil y fácil de usar... Gracias!!!
¡ Gracias por mirar!
Very nice! Any chance of seeing what that stored procedure looks like?
You are a good teacher!
Thank you Peter! That really means a lot. We are learning every day to do this better. 👊
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
Great video, my mind is blown!
That was the hope! :)
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