I have a requirement where the table has a large set of data in database, hence we are trying to parametrized the direct query.
Plan is to append the parameter in the SQL query in the advance editor , something like below :
Select * from Country where Region =‘“&Parameter1&”’"
Here region is column in country table and have created parameter1 based on the list query which is fetching distinct region from the country table.
Later , will like to provide the distinct region from the list query to the user as a filter , which once selected will trigger the parametrized query and get the limited data on the basis of region value.
If they have acces to the file and view it from within Power BI Desktop they can change the Parameter by going to the Home tab, Edit Queries and select Edit Parameters.
However if they only have acces to the report from within the Power BI Service, the only way to change a Parameter would be by going to that reports Workspace, Dataset (select the elipsis …), choose Schedule Refresh and there you’ll find Parameters.
I believe your Parameter must be either type Text or Decimal to be able to be changed but regardless I would not recommend this…
I’m not quite sure where you are going with this but if the user only needs to change the report filtering why are Page Filters not an option or if it’s user specific Row Level Security?
Row level Security has to be implemented as powerbi will be embedded in the other application.
Since the data is really huge, we are trying to restrict the data. Page filters are taking lot of time to pull the data,hence thought of parameterized query so that only relevant data to particular individual username get fetch (RLS)
Yes Melissa is correct here regarding how you can only call the parameter within the query area of Power BI.
This isn’t what I believe you’re looking for when a user is in the online service. It not a simple and intuitive option.
Really your options here are more RLS, or just attempting to optimise the amount of data flowing through into your model more so it isn’t so slow.
Can you reduce the amount of data and columns in your tables using SQL statements. This is the best way to optimise working with large database tables I have found on many occasions.
Usually slimming down the amount of columns is a good idea, getting rid of redundant information or data columns which don’t add much value to the analysis being completed in Power BI.
The other option you have is working with direct query, but this presents other restrictions.