I am working on restructing my queries and stumbled upon the ability to customize a query based on a user input parameter. While extremely useful.. this forces the query to forfeit any query folding capabilities. While my parameter filters down the dataset (and can universally be used to filter all of the data sets I am bringing in), I have a feeling that it slows down the performance as all of the transformations on the data set are all on my machine (vs. at the server).
The parameter is very helpful because it limits my data, and I can easily switch my target data vs. bringing in all of the data (millions of rows) and filtering it at the report level, or creating X different pbix files for each of the districts of the company.
So my question is.. as a rule of thumb, would it be better to 1. place my parameter filter in the query (assume to lose all query folding capabilties because no native query is visible) and complete all transformations at the client level? 2. Load the full data set and place my parameter filter within the transformations? (would lose some query folding capability) Or 3. Load the full data set and just use a slicer at the report level?
Also as a side note... is there any combination of reference query and/or create function (using parameters) to improve performance?
I know that the source doesnt support query folding when using this parameter because 1. "Permission is required to run this native database query" 2. I should be able to view the native query statement of the applied step #"Filtered Rows" (a transformation commonly performed in query folding) yet cannot as it is greyed out.
This tells me that the parameter used in my source query prevents query folding of my transformations to fail?