7

Disabling filter pane aggregates in Power BI

 1 year ago
source link: https://blog.crossjoin.co.uk/2023/04/16/disabling-filter-pane-aggregates-in-power-bi/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Disabling filter pane aggregates in Power BI

When you add a field to the filter pane in a Power BI report and select the “Basic filtering” filter type, in most cases you’ll see some numbers next to the field values:

image-9.png?resize=316%2C439&ssl=1

These numbers are counts of the number of rows for each value in the table that the field is from. The query to get these counts is usually quite fast and inexpensive, but if you’re filtering on a field from a very large table (for example a fact table) and/or using DirectQuery mode that might not be true. For example, the screenshot above is taken from a DirectQuery dataset and here’s the SQL query that generates the counts shown:

image-10.png?resize=463%2C441&ssl=1

Luckily there’s a way to disable these queries and stop the counts being displayed: set the Discourage Implicit Measures property to true on your dataset. The main purpose of this property is to stop the automatic creation of implicit measures when building Power BI reports; this makes sense when you’re using calculation groups, for example, and when you add a calculation group to your dataset then this property is set to true for you. You can also set Discourage Implicit Measures to true manually by connecting to the dataset using Tabular Editor:

image-11.png?resize=479%2C729&ssl=1

Here’s what the filter pane looks like with Discourage Implicit Measures set to true:

image-12.png?resize=312%2C405&ssl=1

Just to be safe, I think it makes sense to set Discourage Implicit Measures to true for all DirectQuery datasets to reduce the overall number of queries run against your data source and reduce the risk of a really expensive query being run. I don’t think seeing the counts adds much value for end users anyway.

[Thanks to Ed Hansberry for pointing out this behaviour and John Vulner for explaining it]


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK