Some of the most interesting work in Power BI occurs when we try to bridge the gap between what Power BI can do natively out-of-the-box and what a customer WANTS to be able to do.

In my opinion – this is where some really interesting learning takes place as we try to stretch the functionality of the program to meet a specified goal.

Problem Statement:

An organization finds value in having region leaders set goals against certain performance categories for the upcoming year. They already have previous years data visualized as a bar chart (below) in Power BI – and they want each leader to be able to set a goal and then have that goal appear immediately in Power BI as the next bar in the chart sequence. As the year progresses – they then want the actual/predicted value for that year to also appear next to the goal.

To solve this we might first suggest that the organization have leaders record their goal values in a survey form and then import the data into the model. However, the organization is adamant about giving leadership the ability to toggle and change between different goals while examining the implications of that stated goal on related data. They want ‘interactive’ goals.

So let’s start with the first problem: Setting interactive goals.

One of the easiest ways to create interactive elements in Power BI is using slicers. However, for this task – we want slicers that won’t remove or change any of the already visible chart elements.

We can accomplish this by creating disconnected slicer tables. One table for each category (4 total). To create each of these tables – we can use GENERATESERIES() to spin up a one column table with values between 0 and 10. 

CategoryB =
GENERATESERIES ( 0, 10, 1 )

After creating a slicer from each table – we can harvest the value of the slicer using a SELECTEDVALUE() equation. 

CategoryB Slicer Value =
SELECTEDVALUE ( CategoryB[CategoryB] )

Now how do we drop that harvested value into the chart? Recall that the AXIS and LEGEND components of a chart visual must be hard-coded in the source table. Power BI will not allow you to put a measure into those fields.

So in order to get an axis/legend label to show up called “2021 Goal” – it must appear in the source table, which means we have to add it there.

We can quickly spin up a new table in Power Query (with our target label “2021 Goal” for Year)

And then append this to the archived data table used to populate the original chart.

After a quick refresh – we can see our new axis component appearing in the chart.

This chart visual is populated using a simple measure called [Score]: 

Score =
SUM ( 'Table'[Value] )

The next step involves replacing the default value of “1” with the number selected in each respective slicer.

To do this, we need to modify the [Score] measure so that when it encounters Year = “2021 Goal” it uses the slicer value for that respective category instead of the default value of 1 in the table.

This can be written using a SWITCH statement: 

Score with Slicer Values =
SWITCH (
    SELECTEDVALUE ( 'Table'[Category] ) & SELECTEDVALUE ( 'Table'[Year] ),
    "CategoryA2021 Goal", SELECTEDVALUE ( CategoryA[CategoryA] ),
    "CategoryB2021 Goal", SELECTEDVALUE ( CategoryB[CategoryB] ),
    "CategoryC2021 Goal", SELECTEDVALUE ( CategoryC[CategoryC] ),
    "CategoryD2021 Goal", SELECTEDVALUE ( CategoryD[CategoryD] ),
    [Score]
)

Now we produce a table with the desired result:

A chart with interactive slicer values embedded!

Users can change the Goal metric and see those changes immediately appear on the chart above.

Now let’s add the second component: “predicted/actual values from the year in progress”.

Let’s say these ‘current year’ values are calculated in the bottom chart – and are derived via a different source table and measure – which has no relationship to the previous table we worked with.

Perhaps the simplest way to add these values to our chart is by duplicating the steps we just walked through:
1. Append “2021 – Actual” to the source table (much like we did for “2021 Goal”)
2. Add new logic to our switch statement that includes the measure when the category, year, and region align.

The modified switch statement (not pictured) – now has 8 terms – and functions exactly as intended:

We have arrived at the desired result!

What’s great about Power BI and DAX – is that there are many different approaches we could have used to solve this problem. This is by no means the only way. Someone else trying to solve this problem might have considered a completely different pathway – and arrived at the exact same solution.