…brew your own Business Intelligence

Enriching your Power BI Solutions with Metadata

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

clip_image002

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.

image

 

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:

image

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:

image

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…

image

6 thoughts on “Enriching your Power BI Solutions with Metadata

  1. JVer says:

    what if you have 50+ KPI’s with each their own definition? They would each require their own table with definition? Model view will be unreadable…

    1. Bill says:

      No, each kpi would only need a row (and corresponding measure) in the one tooltip table.

  2. Great post! You could even use PowerQuery to generate the contents of the meta table directly from the DAX expressions of the measures defined in the JSON schema of the data model. If using Analysis Services Tabular, just feed the Model.bim file into PowerQuery. If using a .pbix model, save it as a Power BI Template, rename the .pbit file to .zip, unzip it and feed the DataModelSchema file into PowerQuery. For Analysis Services multidimensional it will be more tricky, but still doable as the MDX expressions for the measures can also be gathered from the XML of the .cube files.

    1. Bill says:

      That’s an interesting idea… Thanks for sharing!

  3. chandra reddy says:

    Great post. But i think its lengthy tooltips ..and moreover the business users who are accessing the reports will have knowledge on Gross profit logic. so no need to display that i guess. May be last updated is ok.

    1. Bill says:

      yes – the tooltips can get length (not sure what the character cutoff is in pbi but surely there is one)

      i’d disagree on the assumption that (all) business users will have knowledge of the gross profit logic (or whatever the measure is)… sure, some will know… but many won’t (in my experience).

      there are probably better ways to show last updated (not sure why i used that as an example)… typically i’ll design that bit in a separate area (i.e. first page of the PBI report)

Leave a Reply