PowerBI Report builder

[homelander123]

Hi Everyone, 

I have a paginated report thats connected to a semantic model with a dataflow. I used the query designer to bring in the fields and parameters. The report has 50+ columns and 15 parameters. What is the best syntax to be used for querying the data since the report is pretty slow. 

Secondly, while using the query designer its using cascading filters. how can i avoid that?

[v-echaithra]

Hi @homelander123 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @homelander123 ,

Thank you @vigneshkumarcvk  for your inputs.
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you.

[homelander123]

@vigneshkumarcvk  Can you please let me know?

[vigneshkumarcvk]

Hi @homelander123 ,
You’ll get better performance by switching from the graphical query designer to text mode and writing the query directly (DAX or SQL depending on source). That way you can avoid the extra joins and cascading filters the designer introduces. Also, try to limit the dataset size by selecting only the columns you actually need and pushing parameters into the "WHERE" clause instead of relying on auto‑generated cascading filters. This usually makes paginated reports much faster and keeps parameters independent.

If you’d like me to explain the detailed approach step‑by‑step, please feel free to ask.

[homelander123]

@vigneshkumarcvk Yes please! the source is a semantic model within a workspace which is connected to a dataflow. we cant use sql syntax for that right?
Can i have a detailed approach and what exactly would the syntax be?

[vigneshkumarcvk]

@homelander123, As you mentioned, the source is a semantic model connected to a dataflow, you cannot use SQL syntax. Paginated reports against semantic models use DAX queries. Here’s a detailed approach:

• Switch to text mode in Report Builder: In dataset properties, change from the graphical query designer to text mode. This gives you control to write DAX directly instead of relying on the designer’s auto‑generated query.

• Use DAX query syntax: All queries against a semantic model must start with EVALUATE. SUMMARIZECOLUMNS defines which fields you want to bring in. ADDCOLUMNS lets you add calculated measures. FILTER applies your parameters directly.

• Bind parameters manually: In dataset properties, map each report parameter to the DAX query parameter. This avoids cascading filters because parameters are applied independently in the FILTER clause.

• Reduce dataset size: Instead of pulling all 50+ columns, only include the fields required for the report layout. Use SELECTCOLUMNS or SUMMARIZECOLUMNS to keep the query lean.

• Leverage measures in the semantic model: If you already have measures defined, call them in your query rather than recalculating in the report. This pushes computation to the model engine.

• Test incrementally: Start with a small query (few columns, one parameter). Validate performance, then expand gradually. This helps isolate which parameters or columns cause slowdowns.

[homelander123]

@vigneshkumarcvk  Can you please give me a sample syntax for the fields Date, Name, Contact email, and number. 

Date and name are parameters as well

[vigneshkumarcvk]

Hi @homelander123, Yeah sure. 
EVALUATE FILTER(SELECTCOLUMNS('Contacts',"Date", 'Contacts'[Date], "Name", 'Contacts'[Name], "ContactEmail", 'Contacts'[Contact Email], "Number", 'Contacts'[Number]), 'Contacts'[Date] >= @StartDateParm && ''Contacts'[Date] <= @EndDateParm && 'Contacts'[Name] = @NameParm
You can use this way to keeping the dataset lean, and applying parameters directly and ensuring performance is better than pulling all the columns.