HOW TO FIX ‘BROKEN’ VISUALS

…caused by changes that you or someone else made in the upstream semantic model.

When you rename or remove fields in the semantic model that’s connected to a report, it causes the visuals to ‘break’, returning the infamous “grey box of death”.
When you rename or remove fields in the semantic model that’s connected to a report, it causes the visuals to ‘break’, returning the infamous “grey box of death”.

The “grey box of death” in Power BI is unfortunately a common sight for anyone creating reports. When you see this grey error box, it means that the visual can’t obtain a valid result. If that error says “Something is wrong with one or more fields”, then there’s several possible reasons for this:

  • You deleted one or more fields in the model that are used in the visual.
  • You re-named one or more fields in the model (or their table) and those fields are used in the visual.
  • You switched the data source of the report to another model (or version of the same model) which doesn’t have fields that are used in the visual.
A “broken” visual in a report, because a field was re-named in the model and the report doesn’t find it back.
A “broken” visual in a report, because a field was re-named in the model and the report doesn’t find it back.

Generally, these issues are called “broken references”. The report has a reference to a field and table, and when the report can’t find those objects in the semantic model it’s connected to, it returns an error. If you click the green “Fix this” button, it just removes the field. So most report creators solve this by removing the problem field (denoted by a warning sign) and then manually adding the “correct” field back. However, this is problematic for several reasons:

  • It’s time consuming. If you need to do this for several pages, visuals, or fields, it costs you dearly.
  • You lose formatting. When you add the new field, you need to re-do formatting, which is a pain.
  • You make mistakes. It’s easy to miss one or more visuals or fields, causing errors in reports.

There’s a way to solve this problem without manually replacing the fields. In a previous article, I shared a method to do this in .pbix files by using pbi-tools or Python. However, it’s now easier thanks to the Power BI Project (.pbip) file format. In this article, I’ll share how you can quickly and easily fix f

VIDEO TUTORIAL

A 10 min. video below explains how to do this method end-to-end. But feel free to read the article for a short, written explanation.

WHAT YOU NEED TO GET STARTED

To use this approach, you need to save your reports as .pbip files. To enable .pbip files, navigate to: File > Options and Settings > Options > Preview Features > Power BI Project (.pbip) save option. Once you enable it, you’ll have to restart Power BI Desktop. Open your .pbix file again.

To use this method, you need to have the ‘Power BI Project (.pbip) save option’ preview feature enabled.
To use this method, you need to have the ‘Power BI Project (.pbip) save option’ preview feature enabled.

THE METHOD

The following steps describe how you replace a measure, column, or table reference in your report.

STEP 1: SAVE YOUR .PBIX REPORT AS A .PBIP (IF YOU HAVEN’T ALREADY)

The main difference between the .pbix and .pbip format is that a .pbix is a binary file, while a .pbip contains human-readable contents. In the .pbip format, you can actually see the report attributes (it’s metadata or the report definition). There’s many benefits to this, such as a granular view of changes to the report definition (called source control).

This is critical for us, because we can then read the measure references in the report visuals.

Windows Save As dialog saving the report as a PBIP file

STEP 2: CLOSE THE REPORT AND OPEN THE .PBIP “.REPORT” FOLDER

Navigate to where you saved the .pbip file and open the folder that ends with “.Report”. In here is where we will find the information about how the report is defined.

File Explorer with the .Report folder boxed in red

STEP 3: OPEN THE REPORT.JSON FILE

Open the file called report.json. This .json file contains the report definition. It’s a heavily nested JSON that describes each property of your report. For more information about the report.json, you can check the schema on GitHub. I’ve also written about it in a previous series about pbi-tools, here.

File Explorer report folder with report.json boxed in red

STEP 4: BACK-UP THE REPORT.JSON

Everything up until this point is officially supported. Reading the report.json, saving as .pbip, and so forth. However, modifying the report.json is not officially supported by Microsoft, because you could accidentally make changes that “break” your report. For this reason, you should save a back-up of the report.json in case you make a mistake. If that happens, you can replace the report.json with your back-up.

STEP 5: FIND & REPLACE THE FIELDS IN THE REPORT.JSON

With the report.json open, perform a simple “find and replace” of the reference that you re-named. Note that this will replace across your entire report. If you want to replace in only one visual, you need to first find that visual by searching for it’s name or property, then performing the find and replace in a limited selection.

In this example, the measure [Gap] in the ‘Orders’ table was re-named to [Gap to Sales Target]. To target the measure more specifically, we can use known prefix or suffixes, like ‘.’ and ‘\’ (before measures) or ‘\”’ (after measures). In this example, the measure [Gap] in the ‘Orders’ table was re-named to [Gap to Sales Target].   To target the measure more specifically, we can use known prefix or suffixes, like ‘.’  and  ‘\’ (before measures) or ‘\”’ (after measures).

Once you’ve made the changes, you can save the report.json and close it. The next time you re-open the report, it should contain the replaced fields, including formatting and text.

The fixed report. In less than one minute, we fixed the issue with a simple find and replace. When you have dozens of affected fields or visuals, this is a lifesaver.
The fixed report. In less than one minute, we fixed the issue with a simple find and replace. When you have dozens of affected fields or visuals, this is a lifesaver.

TO CONCLUDE

Manually fixing broken references or swapping fields in reports is a waste of time. This is particularly true when you’ve gone through the painstaking effort to place a lot of custom formatting upon the visual. To avoid this, you can leverage the new .pbip format to open and bulk-modify the report.json metadata with a simple find-and-replace. While not officially supported, this saves me a lot of time, and is one of the many little secrets for how you can develop reports faster by not wasting time on mundane tasks.

If you do use this method, be sure that you first take a back-up of your .pbip or .pbix (or the report.json). That way, if you make unintended changes, you can restore the original version.