Dynamic Power BI reports using Parameters
ฝัง
- เผยแพร่เมื่อ 26 ก.ย. 2024
- 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...
it´s not just that your solutions are great, the way your videos are presented is fantastic!
Thanks for the feedback David! much appreciated!
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
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.
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. 👊
Patrick, this was wonderful! These explanations are very well thought out and contextual to common issues I personally run into often with Power BI.
4 years later and you are still helping me!
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!
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 👊
You are a legend! Used the same principle on a web query to pass multiple stations to a string. Love it!
Patrick.......I just feel like giving you a hug. This was fantastic
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.
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!
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.
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.
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! 👊
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.
Exactly what I needed. Thanks for growing my knowledge with a smile!
You are awesome man! I actually understood this first time through, unlike the 1 on 1 tutorial I had at work.
Its first time to use parameters in Power BI your video really helped to understand the basic thanks.
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!!! You are about to take me to school! I love the way you present. This video was very helpful. Thank you!
This was awesome, and I was able to pass two parameters in the same table. Thank you!
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!
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)
Finally.. a clear usage of parameter and function.
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 👊
Absolutely love your presentation style. Really informative content and very entertaining too. Cheers.
Great example. Used this basis to connect to an API with a changing variable. Thanks for the help.
Great video on calling stored procedure from Power BI. Thank you very much.
Thanks for watching Ramachandra! 👊
i loved the way you present and make the things very easy. great
Thank you so much! 👊
I wish all tutorial videos just like "guys in a cube". Thanks !!!
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.
Loved the form and the function, thanks Patrick!
This is really Awesome Patrick.
Kudos to your guys.
Awesome video and clever simple solution!
Thanks Mike!
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
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?
Patrick, Awesome job! You're exceptional at it. Keep sharing.
Great video, thanks. I've been wanting to know how to do this for ages.
Same here. Mind was blown :)
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
I'm looking forward to seeing a video on parameters.
Will get one on the books.
Yeah, more vids on Paramters please. They seem very dynamic feature in Power BI. How to generally utilize them more?
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.
Awesome Video!. Thanks. Could you pass those parameters from a textbox or dropdown button and call the storedprocedure from a button?
Oh and by the way Patrick the videos are great and just the right speed
Thanks Thomas!
Great stuff!! This is exactly what I was looking for the other day!
Glad to hear it!
Nicely done Patrick!
Appreciate that! Thanks for watching. 👊
Hi, great video !!! But I am facing an error when i'm trying to pass two parameters
my code:
[Query="Exec CustomersList ", &StartDate,&EndDate -- but it is giving me syntax error .. can you please help me out with passing two parameters ?
How can this be done when not using a stored procedure, just a regular query imported from SQL?
For scenarios that demand on the fly dynamic reporting, Parameters should be used by end users in a meaningful and easy way,
allowing for complex filtering of the data on the RDBMS side.
for example check out how BIRT does this without breaking a sweat
I am working on a Facebook dashboard where I pass a parameter in the power query mode and this updates my tables and the corresponding charts get updated.
However, I want a way to allow my end user to choose from a list of given values and accordingly the charts on the dashboard should get updated. Need help..!!
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.
Hey @patrick you are super genius bro.
Thanks, Amit! And, thanks for watching.
Hey Patrick, thank you for this lovely tutorial. Can you pls suggest me how to do the same with multiple parameter.
Please reply, stuck in the situation for few days
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
You are a good teacher!
Thank you Peter! That really means a lot. We are learning every day to do this better. 👊
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?
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
Thanks for this great video!
Would be nice if we could pass parameters via variable in the power bi report link
Great Video
Is there a way to use your report with parameters, but passing values directly in the URL?
Thank you very much Patrick !!! How we should proceed if we call a function from sql and we parsing the parameter from a slicer ?
Muy buen trabajo! muy útil y fácil de usar... Gracias!!!
¡ Gracias por mirar!
Wonderful. Instead of parameter values from Excel source, can we have the value passed from URL? Once submitted, the filtered data set must be returned from database itself?
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
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
Awesome content and engaging presentation. Thank you sir!
This was so helpful! Exactly what I was looking for
i have tried loading with sql table. it worked well, thank you. Good stuff
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!
Great stuff, Patrick!
Thanks for watching Alex!
Definitely has some interesting implications. I was just asked about something like this yesterday and wasn't even looking for this video. kinda spooky.
nice! We saw the future for you :) Thanks for watching Jason!
I am new to Power BI and the video is very informative. In place of stored procedure call, can we use a t-sql query - a select statement?
Yes, you can. Select ... where ColumnName = "&ParameterName]
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?
wow...that's hot
great out of the box solution.
thanks !
Thanks! We thought so too!
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.
Unbelievable !!! Thank you very much, this is very much helping
Great job, thanks for the input. I need to do the exact same thing with Snowflake, how could that be done?
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
I'm a bit confused. In your custom function, is it the StudentID Parameter that is called, or the StudentID query?
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?
I have a similar scenario , but the Excel has a StudentLabel for every StudentID . I do not want to display the raw StudentID but the friendlier StudentLabel while selecting the parameter. This was fairly easy in SSRS. How would you do this in Power BI?
Thank you.
PATRICK, YOU ARE A GENIUS!!!
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?
I just wondering if we can choose enter data instead of using the data from Excel? Since we still need gateway for that excel file.
Great video, thanks Patrick! I love your style, and I can imagine several uses for this technique. However, my main use case is not covered: Is it possible to modify the URL based on the selected row? For example, I have a visual that shows totals (measures) by Station. Can I pass the StationID in the URL so the detail report will show the rows related to the summary row the user clicked on?
It will all come down to DAX and what the filter context is. If you can get that StudentID, then you could use that to build out a URL in DAX. You would have to be filtered down to only that one student in that case though.
Very nice! Any chance of seeing what that stored procedure looks like?
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.
Thanks Patrick, I can not refresh this dataset on PBI Service. Please help.
Very nice video Patrick, I have one question though, I am using Power BI 2018 version, I am creating a parameterized query which is in the sql, I am using 'in' operator in the query, I want to pass multiple values to a an ITEM parameter!!! How can I pass multiple values to an ITEM parameter? Here you are using excel sheet as different data source, instead of it, can I use ITEM table and ITEM column to pass the multiple values to an ITEM parameter? How can I do that any ideas?
Excellent video with Great Point
Hi Patrick!
I am trying this approach in the service and the dataset configuration in the service is giving me the following error:
"Query contains unsupported function. Function name: File.Contents"
Can you please suggest a workaround or how to solve this?
Thanks!
Hello Patrick, great video thanks.
This video makes me think of this scenario:
Imagine a business expense report on PBI and add end users who would like to comment on this result using only the PBI site, I wonder if it is possible to combine this solution for this case:
Maybe 1 some visual gets the comment on the page (will be my parameter), 2 identify the other parameters (filters on the page), 3 call the store procedure using this parameters and insert the comment in the database, 4 update the data set (I do not know how to do this for each entry) .
I have not tried to do this, I'm just thinking of another applicability to this new knowledge I've received.
This issue with this approach is that I have all the data already queried. I need a solution where i can use slicers data to query APIs dynamically and populate in a table. like the moment i change the slicer data, the report should go fetch the data from the api and populate a grid below. Kindly share if you have any such solution that you may have presented.
Patrick....This one is amazing. One question I have after the dataset is refreshed my report more than a minute time to get refreshed with the new data.Why?
Why it is not possible to select parameter directly from pbi page through end-user(customer) interactions with visuals(precalculation is expected to be avoided)? I mean invoking sp call calculation by filter or slicer(not parameter field in menu)
thanks so much for the video Patrick ..Is there a way to export the PDFs from Power Bi service automatically or in less time? I need to get PDF report for each student in every class with the filter of each student is selected and they're hundreds .
Thank you.
Lovely video, great feature!
Thanks for watching Ben!
I was looking for this a year ago... Awesome-sauce!
Perhaps even #WasabiMustardSauce awesome! :)
Hi Patrick!
I understand that your example was trying to show a method but is it possible that a relationship between the student ID column and the fact table would have done the job as well?
The student ID table would have filtered the fact table as a report level filter.
Am I missing something here?
Thank you!
This is great Patrick, do you have anything that shows how to pass parameters in Direct query mode than in Import?