Enriching your Power BI Solutions with Metadata

Ever looked at a Power BI report visual and wondered what the logic was behind a calculation?

In most cases, your options are limited to…

1. asking the report author
2. checking the report documentation
3. checking the data dictionary

…all of which… assuming they even exist! …severely disrupt the analytical workflow.

In this post, we’ll see a better approach that involves embedding this sort of metadata inside Power BI solutions and exposing it via Tooltips ultimately providing a more fluid, less interrupted, analytical user experience.

Here’s an example of what we are trying to achieve. It’s the same visual as the image above, but this time we’ve provided additional context informing the user how we calculated [Gross Profit] and the last time this data was updated.

 

As for the best way to accomplish this capability, well, it depends… mainly on the architecture of the solution. For example, is the Power BI solution importing data from a relational source, using DirectQuery against a SQL Server database, using LiveConnect against an Analysis Services database? Or is it built on top of text files or excel?

 

The naïve solution involves hard coding the measure definitions in a calculated table using DAX like below:

Don’t do this!

I chose to do it this way simply for the blog post. This would create a maintenance nightmare! A much better approach is to leverage an external data store… be that an excel file, SQL Server database, etc… and create this in a way that allows it to be easily updated and maintained over time. As long as you’re able to get a table in the solution that looks similar to this:

From this table… called Tooltips… we can create the following measures…

(ToolTip) Gross Profit =
    CALCULATE (
        VALUES ( Tooltips[md_def_value] ),
        'Tooltips'[md_def_key] = "metaGrossProfit"
    )

(ToolTip) LastUpdated =
    CALCULATE (
        VALUES ( Tooltips[md_def_value] ),
        'Tooltips'[md_def_key] = "metaLastUpdated"
    )

These measures are now available in the field explorer and can be dropped in the Tooltips box for corresponding visualizations…