Forum Discussion
Direct Query with Parameter to SQL
Hello. I have a large transactional database. I want my users to be able to enter a CustomerID into a box in an app on the service, and have that customerID be passed back to my SQL Server database and return back just that customer's transactions, using Direct Query because I need up-to-the-minute data.
I have very simple M code with an SQL statement with the Cust parameter:
let
Source = Sql.Database("MTDB", "MTDB1",
[Query="SELECT CustomerID,GroupCode,Potential,Outcome,Status_DT
FROM [dbo].[v_CustomerBrief] WHERE CustomerID = '"&Cust&"'"])
in
Source
How do I create a box where the user can enter the required CustomerID? I am looking at Dynamic M query parameters, but the documentation states that SQL is not allowed. I don't need anything fancy to populate the box to let the user select - they can just type in the text CustomerID to then retrieve the respective transactions. I just cannot figure out how to enable the user to do that. I want to let them change it in an app, not in a workspace. Any help would be amazing.
Anonymous @parameters, @sql
I'm saying that you don't have to parameterize it yourself. With DirectQuery, Power BI will automatically query your SQL source using the filters you've applied as part of the query.
I'd recommend this article for more detailed information on DirectQuery:
https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages
9 Replies
I think this would be easier if you had a slicer where they could select a CustomerID rather than having to type it in. With DirectQuery this is similar to passing a parameter to a query but you don't have to do nearly as much work.
Thank you for your reply. I can definitely add a slicer for CustomerID. What I am trying to solve is how to pass that value to the direct query so that it only pulls back one customer's data in the SQL call. I do not want to bring over the entire customer transaction file because it would be enormous. Can you help me with having the SQL call only pull back one customer's data? AlexisOlson
Enforce single select on the slicer and it should only pull one.
