Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
embassy17orders
Regular Visitor

Passing user selected values to a query to another query

Hello community,

 

Newbie here.  Trying to see if we can use PowerBi as our solution within a Fortune 100 company.  I picked one of our more difficult reports to start since I needed a challenge.

 

Here is what I'm trying to do:

1. Pull Contract Numbers via sql using Oracle/Oracle via ODBC to populate a slicer.

2. The user selects 1 Contract Number via the slicer

3. The selected contract number is sent to the next sql statement, using Oracle/Oracle via ODBC, to pull Payment Numbers related to that specific contract.  Those payment numbers would show up in the next "slicer".

4. The user selects 1 Payment Number via the "slicer".

5. The selected Payment Number is sent to the next very very large sql statement, using Oracle/Oracle via ODBC, to pull detailed payment data related to that specific contract/payment number.  That detailed payment data would show up for the users to view in a report. 

 

Note: I can't just go out to the Oracle Database and pull hundreds of millions of detailed rows for all contracts and payment numbers and then filter from there.  It would take days to return, which is why I need to figure out which contract, then find the payment numbers associated to that contract so the user can select 1 payment number, and then pass the 1 payment number to the very large sql statement (~1800 line sql statement) so that we only get the payment details for that 1 payment number.

 

I hoping this is possible and how to do it. I looked for 3 days at the "go to this link for using parameters" and "go to this link to create a query to create a list to create a parameter to use in the next query" and couldn't find anything that fits the scenario above.

 

Thanks so much for your time and have a great day,

 

Lost in Power BI (Mike)

1 ACCEPTED SOLUTION
10 REPLIES 10
embassy17orders
Regular Visitor

Thanks so much team!  I'm going to attempt the DataInsights suggestion.  I just need to figure out the DirectQuery to Oracle since our company doesn't allow software downloads from outside websites. I have Oracle Client installed, but I need to see if ODP.net is part of that software installation. If not, I may need to utilize my AWS connection instead.  If/when I get this running, I'll make sure to post.

DataInsights
Super User
Super User

@embassy17orders:

 

Mike, try this proof of concept that uses DirectQuery tables and dynamic M parameters. I'm using SQL Server, but I would expect that the solution could be applied to Oracle. The hierarchy is Contract --> Payment --> PaymentDetail. I'm referencing tables directly as opposed to using custom SQL. You could create Oracle views for each of the three tables.

 

DataInsights_0-1651505678734.png

 

Create parameters in Power Query. You can use any value for Current Value.

 

DataInsights_1-1651505914439.png

 

Bind the parameters to columns as shown below. In the Model view, click on the column and go to Advanced Properties to bind the parameter.

 

DataInsights_3-1651506159533.png

 

 

prmContractID --> Contract[ContractID]

prmPaymentID --> Payment[PaymentID]

 

In Power Query, add a step to filter rows in Payment and PaymentDetail:

 

Payment table

Table.SelectRows(dbo_Payment, each ([ContractID] = prmContractID))

 

PaymentDetail table

Table.SelectRows(dbo_PaymentDetail, each ([PaymentID] = prmPaymentID))

 

Create a ContractID slicer based on Contract[ContractID], and a PaymentID slicer based on Payment[PaymentID].

 

Create a table visual based on PaymentDetail.

 

DataInsights_4-1651506510374.png

 

------------------------------------------------------------

 

DataInsights_6-1651506596875.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




So, I'm almost there. I'm running into an issue when doing the following:

"In Power Query, add a step to filter rows in Payment and PaymentDetail"

 

The Payment table has the "source" step to get the data. I tried to create a new step called Custom1 and added the suggested SQL (I changed dbo_Payment to Source and ContractID to Struct_Doc_ID_Num).  I'm getting the following error saying I can't use Direct Query.  Any thoughts?? 

 

Thanks so much,

 

Mike 

 

embassy17orders_0-1651529542187.png

 

@embassy17orders,

 

Are you using custom SQL or referencing database tables/views directly? If custom SQL, you'll need to add the parameter name to the SQL.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This is what's in the source step with the "bolded" text has been updated to make general:

 

= Oracle.Database("ORACLE_DATABASE", [HierarchicalNavigation=true, Query="SELECT PYMT_PKG_ID_NUM, STRUCT_DOC_ID_NUM#(lf) FROM ORACLE_SCHEMA.V_ORACLE_VIEW#(lf)"])

Omg, it worked!!  Thank you thank you thank you.

 

Ok crew, for anyone who does this in the future, follow all the wonderful steps provided by DataInsights. The only change I needed to make was when the following was stated: 

"In Power Query, add a step to filter rows in Payment and PaymentDetail"

I actually had to not add a step.  Instead, within my primary step, I needed to reference the parameter. Please see the code below.  

 

 

= Oracle.Database("ORACLE_DATABASE", [HierarchicalNavigation=true, Query="SELECT PYMT_PKG_ID_NUM, STRUCT_DOC_ID_NUM FROM ORACLE_SCHEMA.V_ORACLE_VIEW WHERE STRUCT_DOC_ID_NUM = '"&prmContractID&"' #(lf)"])

@embassy17orders,

 

Glad to hear it worked! To clarify, there are two ways to achieve this:

 

1. Referencing database tables/views directly (my approach): adding the Power Query step to filter rows is necessary.

 

2. Custom SQL (your approach): instead of adding the Power Query step to filter rows, reference the parameter in the custom SQL.

 

Keep in mind that if you add any Power Query steps after custom SQL, query folding will cease (degrades performance). Referencing database tables/views directly may improve performance depending on how Oracle caches query plans. Also, there are security advantages to using views since a DBA can control access to the views.

 

I believe you can simplify your custom SQL by removing the trailing line feed character:

 

= Oracle.Database("ORACLE_DATABASE", [HierarchicalNavigation=true, Query="SELECT PYMT_PKG_ID_NUM, STRUCT_DOC_ID_NUM FROM ORACLE_SCHEMA.V_ORACLE_VIEW WHERE STRUCT_DOC_ID_NUM = '"&prmContractID&"'"])

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yeah, totally agree there are two different ways depending on what you need to accomplish. Thanks for the heads up on performance issues I need to look out for and good point on removing the trailing line feed.

 

Have a great day!!!!

 

Mike

amitchandak
Super User
Super User

@embassy17orders , I doubt you can do that- Passing slicer value to source connection. It is only possible if you using a direct query and the dynamic M parameter is supported

https://blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-parameters-in-power-bi/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.