Let’s say we need keep track of certifications in a fictional company. Management has requested a Power BI table that should list the region, the certification, the number of certifications, the goal and a concatenation of the number of certifications and the goal. The end goal is requested to look something like this:

This should be easy – just visualize the columns in a table. Unfortunately the last column called “current” is, in fact, not a part of the table. And it gets worse: the columns in the table are from different tables, and we need more than one key. Let’s tackle this in two blog posts.

Multi-column keys

We have two Excel sheets as base data – one tracks the personnel available and one tracks the goals per region. Apparently Dana Scully believes in Azure.

The keys we need for connecting the two tables are “region” and “certification”, respectively. A key on just one of these columns won’t ensure uniqueness and here is hurdle number one: how do we create a relationship in Power BI that is based on more than one key? Simple answer is that we can’t. But what we CAN do is create a concatenated column with the data we need to create a unique key, and then do our relationships based on that. For starters, let’s add a custom column in the personnel table like this:

Then we do almost the same thing in the goals table, but as we only need the actual goal numbers and the key, we don’t need a new column like in the personnel table. We just merge the keys together into one column like this:

We now have the prerequisite keys in place to either merge the two tables together to form one base table or do relations on the fly. I chose the first alternative for this blog post, but either works fine. To create the new base table we’ll do the visualizations on in a bit we do a simple “Merge to New Table” like this:

We’ll call the resulting table “MergedDataTable” in order to keep track of it. After expanding the goals column and renaming the resulting column we are left with this:

Now we have the base table to tackle the second hurdle – counting rows per key. Stay tuned for the next blog post!