When interacting with a Power BI report you may occasionally get the following error:
Couldn’t load the data for this visual
We can’t display this visual because a measure is used in cross highlighting. Please remove the measure or cross highlight.
What does this mean? The most important thing to explain is that you have not done anything wrong: you have, unfortunately, run into a limitation of Power BI where it can’t generate the query needed for a selection on a visual. The issue occurs in some very rare cases when Power BI needs to filter a measure using multiple fields from different tables. Since only workarounds involve changing your visuals, changing the way you interact with your report or changing the way your report behaves, it is helpful to understand the scenarios where you will encounter it.
To do this, take the following basic Power BI dataset:
There are two products in the Product table – Apples and Oranges – and two countries in the Country table – UK and France, plus some sales values in the fact table. All the data in the dataset (minus the keys) can be shown in a table like so:
Note that fields from all three tables in the dataset (the Country field from the Country table, the Product field from the Product table and the Sales field from the Sales table) are used in this visual and Sales is used as a measure.
One way to trigger this error is to select any one of the rows in the body of this table, right-click and select Exclude:
The presence of a measure plus two fields from two different tables is the key to making the error occur: if you remove either the Country or Product fields then the Exclude will work ok. You could also remove the Sales measure, but it’s likely both of these changes will result in the visual not displaying what you want it to display so you’re better off educating your users to expect to see this error if they use Exclude. Selecting the rows you want to keep and using Include instead also works:
Another way to trigger the error is to cross-highlight this table by selecting two rows from different levels in a separate matrix visual on the same page. So, for example, let’s say the same data was displayed in a matrix visual alongside the table visual on the same page:
If, in the matrix visual, you select the France row and the UK/Apples row at the same time, then the error will occur again:
If you use Edit Interactions to stop the matrix from being able to cross highlight the table then you’ll be able to stop the error:
Another possibility is to select rows that are all at the same level to cross highlight instead of selecting rows from different levels. So, for example, if you change the second matrix to be another table and select the two rows for France and the row for UK/Apples, you won’t get the error:
I’m sure there are other scenarios where this occurs (I see this is called out by OKVIZ here for some of their custom visuals) but hopefully these examples are enough to help you understand what’s going on here.
