Direct Query with Parameter to SQL

[dkernen]

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.

@DirectQuery  @parameters, @sql

[AlexisOlson]

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.

[dkernen]

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  

[AlexisOlson]

Enforce single select on the slicer and it should only pull one.

[dkernen]

I am sorry, but I just don't understand.  Here is a very simple sample file.  (CustomerID = Referral_ID).  How do I make the value of the slicer as my Referral and have my direct query use that as a parameter for the SQL call?  @AlexisOlson 
https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/ERhNpctEl3lCr47sFvB6fC0BG8l0B6ezoU4Zk... 

[AlexisOlson · Accepted solution]

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

[EnderDavila]

Hi, I have a similar situation. If I don't make use of a parameter, it would bring (or try to load) the entire database, which would take a really long time doing this at first instance. Even if I let it load the entire database, when I search in the slicer for what I want, it also takes a long time to even search for that. It's not efficient at all. That is why if you limit the imported data into power bi, it would be such a successs. Parameter is the way to go. I found a way to do it but it doesn't work in power bi service because it doesn't detect any table imported into the report. I'm looking for another way to do being able to use Power BI Service (published report).

[beaumokrane]

Hello all, I have a similar situation. I use a directQuery with parameters. To simplify to only one, it's a transaction ID and the database can have up to 10000  entries a day and about 15 years of data , so loading all of them  into a slicer is not something I am condering because high volume of data for no reason. The ideal solution, given the fact that users know which transaction they want,  would be a simple dialog with an input box and a "GO"  button. It doesn't seem possible to do this with Power BI. My transaction ID is in the form of: YYYYMMDDnnnn where nnnn is from 1 to 9999. So I created 2 slicers one for the day and one for the ticket. This way I don't have to load all the tickets. The way I see it working is the user would select one entry from each slicer, I would capture that and concatenate the 2 selections into a measure and pass that to my query.  I'm new with PBI and I have been struggling with this for while. The challenge is to find a simple way to update the parameter from a measure or a visual. Any suggestions would be great? Thank you in advance.