Cross highlight when using direct query in PBI

[Guest DanyHoter]

Cross highlight/ Cross filter

 

 

 

 

Cross highlight/Cross filter is the default behavior of PBI when clicking on one visual causes other visuals in the page to recalculate.

 

The affect can be of regular filter, like selecting a value in a slicer that will filter all other visuals, or it can be cross highlight which causes visuals to show the highlighted and not highlighted values in two different shades.

 

 

 

 

Some types of filters cannot show highlight values like line charts or tables so the effect on them is always filter.

 

 

 

Why is this behavior bad?

 

 

Every time a user clicks on a row or a column in a visual, every other visual in the page feels an urge to refresh itself and respond to the click.

 

Visual that are based on direct query may issue multiple queries for each refresh.

 

Depending on the number of measures used in the visual, the number of generated queries can be as high as 10 or even more for a single visual.

 

A single click can trigger tens of queries to the ADX backend (Or another source) and can be very expensive.

 

 

 

What can be done to avoid it?

 

 

 

 

You can control how each visual interacts with any other using the edit interactions feature under view:

 

 

 

 

 

 

 

 

If you have a busy page, it can a pain to disconnect every visual from every other visual.

 

The icons that describe the type of interaction

 

 

 

 

can be hidden behind other visuals.

 

Still, it is a time well spend.

 

 

 

Should you avoid any interactions?

 

 

 

 

Definitely not.

 

A clear exception is slicers. You want slicers to filter almost all other visuals on the page.

 

Maybe you don’t want slicers to filter each other.

 

You do want a filter on countries to interact with a slicer on states, but probably not with a slicer on products. Each such interaction can trigger expensive queries against your largest tables.

 

Sometimes, you do want to select in one chart and see the highlighted values in another chart.

 

This should be the exception, not the norm.

 

 

 

Can you avoid this exercise from the start?

 

 

 

 

Yes you can if you change the default behavior upfront.

 

In the options are there is an option called query reduction

 

 

 

 

From here you can disable cross highlights by default.

 

You can still change the default of no interaction in specific cases.

 

 

 

 

 

What about filters in the filter pane?

 

 

 

 

Filters in the filter pane also cross-filter each other.

 

If the filters come from the same query and the query is not too big, this is acceptable.

 

The problem starts when filters belong to different tables and the only way to cross filter involves the fact table. In these cases , cross-filtering can become very expensive.

 

There is no good away at this point to avoid this behavior and the only advice is to monitor closely the generated queries and. Filters have advantages but on the other hand, you have no control over interactions.

 

It is very important to use the query reduction features and to apply all filters in one go

 

Attached report

 

 

 

 

In the attached report there is a page where all visuals interact, a page with minimal interaction and a page with filters.

 

Using .show queries, you can see the number of queries generated in each case.

 

Continue reading...