Skip to main content
Version: Next

Query Settings

You can view and modify a lot of cube-level settings for queries.

Factless settings​

  • query.factless.ignoreIncidentalfilter: Whether to ignore unrelated dimension filters in dimension-only queries. Disabling this may increase the time it takes to load filter screens in applications like Tableau. The default value is TRUE.
  • query.factless.useIncidentalFacts: Whether to allow dimension only queries to contain unrelated dimensions in the results. The default value is TRUE.

Language settings​

  • query.language.dax.bypassIsBlank: Whether to bypass IsBlank for measures in DAX. Enabling this bypass can dramatically reduce some PowerBI query sizes. This can technically produce wrong results in some circumstances. The default value is False. For more information, see Known Issues.
  • query.language.dax.format.validate: Whether to enable the validation of the custom format string. The default value is True.
  • query.language.mdx.drilldown.allLevel.suppressDynamicMeasureGrandTotalExcel: Suppress totals for calculations containing dynamic members that will produce empty results. The default value is True.
  • query.language.mdx.drilldown.allLevel.suppressExcelHidden: Suppress drill-down cells not displayed by Excel. This reduces the number of queries, which may improve query performance but the results will be incomplete and may incompatible with other MDX clients. This also hides grand totals in some queries. The default value is TRUE.
  • query.language.mdx.drilldown.allLevel.suppressSubtotalExcelHidden: Suppress drill-down cells not displayed by Excel. This reduces the number of queries, which may improve query performance, but the results will be incomplete and may be incompatible with other MDX clients. This does display grand totals. The default value is FALSE.
  • query.language.mdx.slicers.allowHideNoData: Use filters, measures, slicer lists, and/or rows and columns in the pivot table to determine which members in a slicer list will be hidden when 'Hide items with no data' is selected. This option uses the 'slicers.indicateNoData.X' flags to determine what is constrained. This may affect performance. The default value is False.
  • query.language.mdx.slicers.indicateNoData.useConstraints: Use filters in the pivot table and other slicers to indicate which members in a slicer list are affected by those constraints. This may affect performance. The default value is True.
  • query.language.mdx.slicers.indicateNoData.useMeasures: Use measures in the pivot table to indicate which members in a slicer list have data in the measures' fact tables. This may affect performance. The default value is False.
  • query.language.mdx.slicers.indicateNoData.useSelections: Use displayed slicer lists and rows and columns in the pivot table to indicate which members in a slicer contain data. This may affect performance. The default value is False.
  • query.language.mdx.subquery.limit: The limit of sub-queries generated for MDX queries. The default value is 500. If an MDX query fails the limit check, then the aggregate system would not attempt to build aggregates based on the violating query.

Other settings​

  • query.result.max_rows: The maximum number of rows that can be returned for a query. The default value is 200000.
  • query.slowQuery.cutoff: The query duration cutoff for a completed query to emit a SlowQueryEvent. The default value is 4 seconds.
  • query.planning.unsecureTotals.enabled: Whether to allow the unsecure totals flag to be applied to security dimensions. The default value is False. For details about the Secure Totals option, see Creating a User-Based Security Dimension.
  • query.planning.defaultHierarchyOverride: The hierarchy to use when queries require a qualified hierarchy, but none is present. Be aware that this only applies to the dimension that contains the specified hierarchy. For more information, see Referencing Calculation Groups in Calculated Measures.
  • query.planning.simplifyNullSubqueries: Enables the removal of FROM clauses from non-join queries or subqueries that have been planned to only return NULL. This setting is enabled by default.
  • query.joins.optimization.simplifyNullJoins: Enables the removal of FROM clauses from join queries or subqueries that have been planned to only return NULL. This setting is enabled by default.
  • query.joins.optimization.simplifyNullSemiAdditives: Enables the removal of FROM clauses from semi-additive subqueries whose measures have been planned to be NULL. This occurs even if there are dimensional selections along with the NULL measure. This setting is enabled by default. Note that it is only applied if the QUERY.PLANNING.SEMIADDITIVE.FORCEINDIVIDUALMEASURECHECK engine setting is also enabled; for more information, see Query Settings.

More information​

Cube Level Configuration Settings