Finding similar or duplicate reports in Power BI and Microsoft Fabric

The challenge

Especially in self-service BI scenarios, it’s common for teams to create new reports for every specific use case, often not checking (or being able to check) if a similar report already exists.

Over time, this leads to a sprawl of similar reports that differ in filters, visuals, or formatting. Having so many similar reports increases the risk of losing control and creating a ton of redundancy.

How we compare reports now

Even if I look at two very similar reports from a .pbip or .pbir point of view, it is still very difficult to determine the actual differences (PBIR format for Power BI reports). For PBIX reports it is near-impossible.

While PBIR slightly improves this, pinpointing the main differences between reports is still difficult and usually limited to comparing two reports at a time. This makes the approach hard to scale and ineffective at objectively measuring report similarity.

Introducing Report Similarity in Measure Killer

Thus we added a new feature to Measure Killer called Report Similarity. When analyzing reports, Measure Killer requires a lot of metadata which is organized in a structured manner. With this information, it is possible to compare reports in a very intricate and customizable way.

Report Similarity evaluates reports across multiple attributes and assigns a similarity score to each comparison. This score tells you how closely two reports resemble each other, while still allowing users to drill into the exact differences that contribute to that score.

The comparison includes attributes like:

• Name

• Description

• All pages

• All custom visuals

• All report-level measures

• All filters (report-level, page-level and visual-level)

• All bookmarks

• And many more traits

Each of the attributes can be disabled or enabled and different weights can be set to rank their importance. This allows comparing multiple reports in almost any way.

Getting started with Report Similarity

To get to this feature, select either the Tenant Analysis or Limited Tenant Analysis modes in Measure Killer's start window and analyze the reports you want to compare. Afterwards, click on the "Report similarity" button located on the top, it will be enabled as soon as you ran a Power BI report via the Selection tab.

Accessing Report similarity in Limited Tenant Analysis.

Now you can select reports that are you want to find similar ones for:

Selecting the reports to find duplicates for.

In the next window, you will be able to select the reports to be compared with.

In our case we want to compare the "Power BI Report for Switzerland PBIR" with all other reports we have access to, to understand if any of them is very similar.

Selecting reports to be compared with the ones previously selected.

Understanding the results

After this, Measure Killer will assign a similarity score between two reports. The higher the score, the more similar the reports are.

Obviously we want to see what exactly is similar and what is different, we can go to Similarity details to see that.

Similarity scores for each pair of compared reports.

Similarity scores use a red-to-green color scale, with red signaling high similarity, which is typically a bad sign. In this example, the Power BI Report for Switzerland PBIR report has an almost identical report called Power BI Report for Switzerland calc groups:

Similarity details between two reports, displaying the different attributes being compared.

Power BI Report for Switzerland calc groups is basically an exact copy of the original Power BI Report for Switzerland PBIR, but it is not using the PBIR format. By expanding some of the items, it is possible to see other small differences as we can see below.

Comparison details between Power BI Report for Switzerland PBIR and Power BI Report for Switzerland calc groups.

When comparing our Power BI Report for Switzerland PBIR with another report, the Switzerland Trade Report, we can see differences for custom visuals, bookmarks and the mobile layout of the report (see below)

Comparison details between Power BI Report for Switzerland PBIR and Switzerland Trade Report, highlighting the differences between custom visuals, bookmarks and the mobile layout.

To change the weights or to disable some of the attributes to be compared, simply click on "Adjust weights" when selecting the reports (in the window before):

Option to adjust the ways to create a custom comparison.

Here, the weights can be customized:

Customizing the weights for report similarity.

Final thoughts

Report Similarity provides an easy and customizable way to understand how similar Power BI reports are. By creating a similarity score but also comparing every single item (every page with the best match in the other report and so on) we can almost do any comparison to achieve our goals.

If you want to try out the full version of Measure Killer, you can request a trial here.