Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hoping someone can help me with this...
I've read the "Deep Dive into Query Parameters and Power BI Templates" article about the use of parameters in Power BI Desktop . Further to this, I've been trying - unsuccessfully - to make use of a parameter in a simple test SQL query that accesses an Oracle database.
Let's say I have a simple query:
SELECT col1, col2
FROM mytable
WHERE col3 = 'mytestvalue'
I then create a parameter called "myparam", which I'd like to use instead of the literal value:
SELECT col1, col2
FROM mytable
WHERE col3 = &myparam
I've tried various ways of expressing the parameter, e.g. &myparam& / :myparam / "&myparam&" but none have worked - I just get different Oracle errors raised, e.g. ORA-904: invalid identifier, ORA-1008 not all variables bound.
How should I express the parameter to get this to work?
Solved! Go to Solution.
This will help on thi subject. For creating a parameter from Orcale source or using a parameter in Orcale query.
Hi ankitpatira
Thanks for your response.
Unfortunately, the examples given in these articles don't seem to address the specific scenario that I'm encountering when trying to use the parameter as a filter when accessing data from an Oracle database.
I know, it is too late; just to close the loop and share solution for others who are looking for working solution.
Step#1: Create Parameter pID [text parameter and keep default value]
Step#2: On Dataset that is pulled on Query Editor, right click and go to Advanced Editor. Edit below code as per your database and table name.
let
pID=ID,
Source =
Oracle.Database("your database name",
[HierarchicalNavigation=true,
Query="SELECT * FROM MYtableName#(lf) where DA_LOAN_ID='"&pID&"'"])
in
Source
Thank you, kailas684. You're a star! I spent hours yesterday trying to get this to work. This morning I set my parameter type to text and used '"&Parameter&"' and it finally worked. I can now bring back 1 record instead of 20,000
Thanks Kailas684, this sort of worked for me. My requirement is to pass a list to the sql query from the parameter list.
Insead of this where DA_LOAN_ID='"&pID&"'
I want something that is where DA_LOAD_ID IN ('"&ParameterList&"')
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
79 | |
67 | |
60 | |
45 | |
45 |