Dynamic Power BI reports using Parameters
HTML-код
- Опубликовано: 2 окт 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...
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
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'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.
Its first time to use parameters in Power BI your video really helped to understand the basic thanks.
Brilliant, this was the only comprehensible explanation of parameters I could find! 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!
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.
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 stuff!! This is exactly what I was looking for the other day!
Glad to hear it!
This was an amazing tutorial! Love the delivery! You got yourself a new fan!
Muy buen trabajo! muy útil y fácil de usar... Gracias!!!
¡ Gracias por mirar!
that is awesome way to let users interact, but can different users use the same dashboard with different parameters?
Thanks @Patrick, I use the steps and the transformation is successful now. After the transformation is over I have around 2Milliion rows. While loading all the data to Power BI (Interface) I am getting 429 errors. Is there any way to workaround it. If you have any video on that please do suggest or any article for reference.
Awesome trick Patrick. Thanks!
Most welcome David!
Loved the video but I dont think this is the most efficient way. Creating the parameter, the custom function, and then invoking. You could actually just have taken your excel sheet in the power editor and merged your data source data into in. Or used a filter on the datasource column itself and pass the excel column as a filter; List.Contains(#"studentids",[studentid]). Both are faster from a data load perspective, especially with huge datasets
I need to avoid run permission I had pass 16000different parameters can you helpon it
Hello mam, I understand this is an old post, but I'd be grateful if you could send that file sample to my email address so that I can practice with it. I sincerely appreciate it. Thanks in advance.
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?
Hi Patrick,
nice video.
Can we do the page by page interaction with in the same report
Hi Patric, Fantastic! I always like your topic and way to present. Regarding to this topic....Can power bi allow cascading query filter. E.g. on selection of Parameter A value, dynamically change the Parameter B values. If I select Param A as 'Region' (Param A = 'Region', 'Country', 'Group') then Only Region related values can be show in Param B instated all or null. Currently I'm planning to give only one parameter which is combination of Param A and Param B. Greatly appreciate on comments & video as always. Thanks.
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!
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..!!
Can we use this approach with DirectQuery? I know the DirectQuery only supports a single data source in most cases. How would it handle the Excel workbook providing parameter values?
Patrick, thank for great video. Good to learn from your demo. However, could you be able to show me how to pass 2 dates parameters (Start date, End date) from SQL store procedure to parameters in the Power BI so that user can select any date range from Power BI to display data report . I have an issue to deal with format date from SQL store procedure and date from Power bi with error message "We cannot pass value EXEC... to type Function. Thank you.
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?
Man... you are just awesome ! that gives me so much possibilities ! Thank you very much ! Excellent !
need a parameters deep dive!
I want to ask if this feature available in power BI,
I have a web site 'portal', I want to show power by Report inside portal using Link Or button , the report must be shown based on the user ID That is Login, such as if user id = 500 the report must show all data for user 500, if the get coming user id = 520 the report must be shown ll data for user 520.
Yes, that is possible! Search for "Row-level security (RLS)".
Interesting stuff, is there a way to dynamically do this with tabular models instead of passing filter params in the address to the published report which exposes these to the user?
Thank you for the video. How do i pass a parameter from .net into power bi dashboard when it is embedded as a service and uses app own data. any pointers would be a great help
I am also interested in finding whether there is a way for doing this. Per the research I've done an year ago, such a thing was not supported, but maybe support was added in the meantime? Or maybe I missed something... Patrick, could you share any information you have on how can parameters passed from an end to the other (i.e., from the web app embedding the report using Power BI Embedded all the way to the source of data, for example an Oracle database). Thanks a lot!
Yeah but if you have to enter the ID in excel might as well create the report in excel. I want the use to enter one ID in a text box in the PBI service report and refresh
Good video but man you were racing through it! Slow down for us old guys!
Appreciate the feedback!
Hi Patrick, I embedded a PB report via Azure Portal function method. Is it possible I can pass the patameters into report via Embedded URL (token) in that case? Thank you very much!
My source is SQL server for seven customers with same schema, means seven different instances. How to implement the same parameter based on the client id value passed. Please let me know. Thank you!
Hi...i am currently working with parameters in power BI desktop and i was able to do that..like users will have the ability to enter values from drop down list and then submit the report.but i cannot find a way to enable " select all" values in the parameter drop down list so that users can select all the parameter values at one go.can you please help!
Thank you very much by this lesson!
Awesome Video!. Thanks. Could you pass those parameters from a textbox or dropdown button and call the storedprocedure from a button?
Hello Patrick! I am new to Power BI and Power Query. Can I ask you, do you know how to get the URL from my publish webpage and use it in a Power Query IF statement so I can access different XML files depending on the URL?
Thank you in advance for your help!!
Great job, thanks for the input. I need to do the exact same thing with Snowflake, how could that be done?
Hi Patrick,
could pls help me up on :how to do this dynamic param passing on a select query of a table and the dynamic param shd be passed to the where clause
I was trying to learn Power BI. Microsoft offers tutorials for that. Unfortunately, they are so badly written, they just lack the visuals, which is ridiculous considering the app whole point is to do visuals.
Your video seems to cover the same content on Microsoft's page, point by point (I am not sure who copied who). However, instead of simply telling me what to do, you are showing me what to do. Big difference...
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?
Thanks Patrick, I can not refresh this dataset on PBI Service. Please help.
A little off topic, but how do you have your power query in darkmode?
Nice video! Very useful! Thanks for sharing. One question: could be another query instead of excel file? Thank so much!
Absolutely. The concept works with other data sources as well. Not just Excel.
Thanks!
Is it not possible to pass value in one button which could be dynamic?
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!
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.
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?
Hello Patrick,
In this video, you call a SQL statement with the sentence EXEC GetStudentDetails 7780097. What is GetStudentDetails? A stored procedure? What is its sintax?
Thanks, great video!!!
Correct. It is a stored procedure.
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.
does value native query with parameters do the same thing?
Hi,
I am getting data from an API that has two parameters like below in the URL:
Param1930=2021-01-01&Param1931=2030-12-31&format=JSON
These are the start and end dates of the report that I have manully set up in the API URL. But, how do I make it dynamic so that eveyday that I refresh the data in Power BI connect to the API and pull the latest data? My current M code is something like below:
let
Source = Json.Document(File.Contents("C:\Users\Ali\AppData\Local\Microsoft\Windows\INetCache\IE\EHD7MHFN\1120")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"txtProvider",
"txtIncidentNo",
"FltDate",
"LogTime")
Hi Patrick , can you show me how to use this in flat files from folder.
Say , I have 3 Excel sheets in a file and I have 3 such files in a folder in my OneDrive.
Each file in the folder is going to be a table in power bi.
I need to pull all these data from folder and do dynamic transition to b a table using parameters.
Iam helpless please make a video on this
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!
Sir ,
Please help me to load data using Sql procedure
let
Source = Sql.Database(".,2014", "demodb", [Query="EXEC SPM_CUSTOMER_LIST "&Locationid & fromDate & Todate])
in
Source
Its Not working how can we pass multiple
Like SSRS
Date selection as parameter ?
Absolutely love your presentation style. Really informative content and very entertaining too. Cheers.
Hey good job and kudos Patrick,
I do have a below challenge
1. I created a Dashboard which is connected with 10 different csv files which i kept in a folder.
2. On daily basis I need to dowload the csv file and upload in Power BI desktop
3. Daily I'm changeing the source file location, but file name doesn't change, only changing the sub-folder like "/11012021/", "/11022021/", etc.
4. How to make happen easier to take the latest sub-folder and the related csv files?
Would you please write the M Language generated when you created the function? Thank you!!!
I know this post is already 3 years old. But I see no complains on the method described: does that mean that it really works? In my case it is not working. I would rather say it is working in Power BI Desktop but fails to refresh when published to the Power BI service. I've successfully configured on-premises data gateway. But the refresh gives an error: Unable to combine data] Section1/RM Items/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. I hope Patrick will read the comment and answer what to do. Regards Adam
This is a great bit of information. Still, I wish PowerBI would work like SSRS in terms of parameters:e.g. dynamically load a list/combobox of all students from the datasource. Then the user selects a few students from the list and PowerBI goes back to the datasource to retrieve the enrolment records for just those students.Does someone know how to do this? Too much to ask for?Still, great video and much appreciated.
Hi P Mack - I'm also looking for this myself. Did you sort if out yet? If so could you share a bit of info? Thanks.
OMG. This solution is almost the exact opposite of what I need to figure out how to do.. (Maybe there's a super easy way to do this that I just don't know about?).. Maybe you can help!
Okay, I'm trying to find a way to call a sql server procedure from Power BI when a 'send drill through' button is selected that will pass parameters such as the end user's username (for RLS filtering in final report) and the reports' current filters as they have them set. This set of parameters should then be applied to a separate table with much more granular, unaggregated data that is on the server. At this point, SQL Server should query the aforementioned target table using those applied filters. And finally, send the filtered results in xlsx or csv format as an email attachment using sp_send_dbmail. If I could at least get an idea of the methodology used to pass and save the parameters to a table in sql server, I could figure out the email procedure..
This issue seemed impossible (and maybe still is) until I saw this video.. Have you done a video showing anything like this in the past? If not, could you make one?
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?
I have a fact table with start_year (2020) and end_year(2023) parameters in it to limit the number of years worth of data. On top of this I have incremental refresh of last 13 months. I publish this file to web service and change the start_year parameter say from 2020 to 2019. Why does the parameter changes in web service doesn't take into effect? If I change from 2020 to 2021, it does make necessary changes but for the first time only after publishing the file. Any thought on this? My report should only hold only last 5 years of data with last 13 months of incremental refresh.
please help ?
Details: "Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)"
Seems I'm the odd one here. Following along, everything seemed great until I inserted my parameter name in the SQL query. The advanced editor shows no syntax errors but when I click "Done", there are errors. Even after the warning sign displaying on the "Query 1" button and I edited the permission, the error keeps appearing this has been my problem for day. Kindly help me address this, "Guy in a Cube".
I am trying to create a Dynamic Power BI report using SQL Server Stored Procedures with Parameters. My questions (1) Can I use DirectQuery instead of Import for my Data Connectivity mode. (2) How can I pass the value from a sql query or table into the stored procedure parameter in Power BI and get the data to be refreshed in the Power BI Reports. (3). How can users select a parameter value from the Report, so the stored procedure can run for different parameter value. (4) How do I create a checkbox or a dropbox on the report for the parameter value or can a slicer be use to pass the value to the stored procedure.
I have question Patrick..
Your scenario is very similar to my issue.. So I'm guessing I've found "the" wise person who can help me :D
My client wants parameterized data filtering in the sql stored procedure used for the dashboard.
The challenge is that the query itself uses a dynamic parameter.
And the second limitation is that we are using on premises power bi (not the online version).
So could you let us know if it's possible?? If you need more details plz share your email ID.
No. It is not dynamic way of pulling data from SQL database. It is rather step-like method. You cannot expect any mortal user to enter parameter that way. I could imagine that from small dimension table I select student name (or ID) by slicer or other visual and then after that the query is executed dynamically select * from FactTable where ID= SelectedParameter. That would be useful - the big FactTable would not have to be loaded in full but only chosen rows.
Great Video! how would you do this using GET API Calls links?
Example: Http://test/API/Students/?id=112
Dynamic Parameters for 112 in PowerBI
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.
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 👊
Could have just changed the SQL WHERE clause to use an IN()... then in parameters they just paste with commas between - no need to link to other stuff....
What I really want to know, is how to get it to create a sql query dynamically by changing table names, attributes and business date for the filter in slicers and retrieving the data in a matrix. I saw a guy do this with R... but he didn't retrieve different sets of data
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!
How about passing a parameter value from a URL into a Power BI Direct Query parameter from Power BI service? I watched the URL filter video, but the data can not be pre-loaded. Need to pass a URL parameter into a Power BI parameter, execute a direct query, and display the data in Power BI service. Is there a way to do this?
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?
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.
Hi nice video
I have one doubt
You are prefixing “&” infont of parameter which is varchar type in advance editor.
I’m trying to give parameter of int type.
What should I prefix before that in advance editor.
I tried &, # but it is throwing errors
I tried without giving special character also.
But it is not happening.
Please help me out.
Awesome!!! Can we think one step ahead? Let's say - I have columns for subjects, student admin. no. , and the email id. I want to send the personalized result to their email id. Any thoughts before I tear all my hair out? No Row level security please.
Thank you for video tutorial.
I have a question though.
Is it possible to place filter in a Page of report that referring to Parameter.?
Detail - I have created one Parameter say Param_P and created one Power BI report with page named Page_P, now i want to create filter on page which referring to the Param_P values, in short, can parameter will be used as a filter on visual of the report and user can change the values from the filters itself
Note - Don't want to change into PBI Template, need to have normal filter referring to Parameter
Thanks
Amit Srivastava
it's quite easy and native to add a filter box (textbox, radio, mutil) in SSRS or tableau. once user changes its value, the SSRS or tableau can issue a query to database, and refresh data. but it's a disaster when i'm trying to do this using PowerBI with parameters.....
Hi!
I have a store with a parameter but i need to call that parameter from other query, for example
exec proc_Test where Date = @date
Then i call that parameter from other querie that contains only dates so when i modify that date from the other query the Store will be refreshed.
that date paramater will be in a filter for example, i tried everything...can you help me?
I have Pro license.-
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.
I have a request to use parameters for start and end dates. However, I will not able to bring that date field to filter inside the dataset. One option I have is to use stored procedures and pass parameter to a stored procedure. I am trying to find another option.
Example: paydate >= startdate and = @StartDate and paydate = @StartDate and paydate
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!
There is just one matter i didn't understand. If report is dedicated for only one person and that person has the excel workbook that's OK. But what if the report shared to multiple users. How will they edit excel workbook? Is there a solution about editing values without opening excel workbook.
wow...that's hot
great out of the box solution.
thanks !
Thanks! We thought so too!
Hi, i have one question, I maked all the steps and it results just like i want, but after all, I added a new column in my stored procedure script, but I can't refresh my sotored procedure in power BI, the new column is not visible, and the only way I found was start again configuring all the steps, how can I add my new column?
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)
Hey Patrick, is there a way client can change the parameters from the front end and doesn’t have to go to the edit query section ??
Parameters are a data load time options and not usable from an end-user perspective. As the report owner, you can update the parameters from the dataset settings screen or from a REST API perspective.
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.
parameters are still a bit of a "mistery" to me. to "little" things I just connect the excel online sheet to the powerbi directly to the web version. Any excel online changes = power BI web changes, right?
But i find this solution, very cool!
Q.. How can I pass the value in mysql query dynamically from the report section whatever user selected in filter
1) users select a single date that date should pass on my store procedure which I am calling in power bi while fetching the data from database
How can I achieve this please help.
Hello, I want to pass user logged in through parameter to snowflake query. Please provide inputs how to implement this scenario. Thanks in advance!
Hi ..I have a requirement of dynamical filter to choose absolute value or % to be displayed. Can you let me know if you have solution for same.For example I have a table with Location and respective employee count. In dynamic files user should see option to select value or % when they select % automatically the table shold show % of grand total instead of count of employees.
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.
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.
Cool video. It won't work for me though. I am getting errors because the values in my parameter (server names) have hyphens in them and I cannot seem to get it to work. I can do the initial load from the stored procedure, but when I replace the source with the created parameter, it gives me a syntax error (Incorrect syntax near '-'). I assume maybe there is a way to use escape characters or something? I cannot find an answer online.