Power BI Field Parameters and Slowly Changing Dimensions

Power BI field parameters are a new feature in Power BI Desktop, and it’s one of the best of the past months. In short, Power BI field parameters allow you to easily switch between dimensions attributes or measures in a filter. Previously, you had to do all sorts of DAX wizardry to make this happen, but now it’s just a couple of clicks.

The goal of this blog post is not to tell you exactly how they work, but rather showcase an interesting use case. You can find more info about Power BI field parameters in the official blog post, but also here, here and here. The use case I’m talking about is slowly changing dimensions of Type 2, you know, the one where we insert a record for every change. Often, I also include an extra column for each column of which we’re tracking history: the “current value column”. For example, if we keep history of the department for an employee, I have a column “CurrentDepartment”. If a type 2 change occurs, the values of this columns are updated to the last known value for this dimension member. This allows to answer different types of questions, because sometimes users are interested in the historical values, but sometimes they just want to know the current value.

Allow me to illustrate this with an example. A couple of years back, I did a project at a big law firm. Every case is assigned to a lawyer. For each case, there are certain metrics that play a role in determining the bonus of the lawyer at the end of the year: accounts receivable, work-in-progress, revenue et cetera. If a lawyer had a lot of accounts receivable open (meaning a lot of invoices that were not yet paid by clients) or a lot of WIP (work that has been done but not yet invoiced), this negatively affects the bonus. So I asked if they wanted to track history of the assigned lawyer in the data warehouse.

“No, that’s not necessary”.

“OK. But what if a lawyer leaves or is fired, and his open cases are assigned to another lawyer. And on those cases there’s a lot of accounts receivable and WIP, because that previous lawyer really had a messy administration. Should this affect the bonus of the newly assigned lawyer?”

“Ehrm…”

Suffice to say, implementing a decent type 2 SCD was suddenly a priority. OK, but what does this have to do with Power BI field parameters? Sometimes the partners of the office wanted to see the historically assigned lawyers (to determine bonuses for example), but in other cases they wanted to see the current lawyers on the open cases, for example to have a view of the current open accounts receivable and who they can contact for more info. This meant they either had to have two visualizations on the dashboard, or have a means where they can change it themselves (like in a Pivot Table). Power BI field parameters make this a lot easier!

Suppose we have the following oversimplified sample data:

We have a certain case, and for each month we track some measure and which lawyer was assigned to it. We also have a column “CurrentLawyerCode” which stores the code of the lawyer current responsible for the case. Before field parameters, we needed two visuals to answer our questions:

We can create a new field parameter, calling it “current or historic” which allows us to switch between the two lawyer code columns:

And now we can easily switch both by using a slicer and one visual:

At a current client, we have a couple of SCD Type 2 dimensions, and each of those has a couple of “current columns”. Power BI field parameters have really simplified our dashboard layouts and made the work of the analysts easier.