12- Disconnected Tables

A disconnected table is one that you add to your PowerPivot model but intentionally do not relate it to any other tables. At first that may seem a little strange – if there is no relationship between it and any other tables, filter context can never flow into it or out of it, so a disconnected table would never contribute anything meaningful to a pivot involving other tables.

But once you learn a simple new trick, it will make sense. It helps to have an example.

A Parameterized Report

Let’s work backwards this time: I will show you the result, and then explain how I did it.

Take a look at this pivot:

Figure 140 Just a simple little pivot with two slicers, right?

Nothing exciting on the surface. But let’s change that “USD per EUR” from $1.10 to $1.80 and see what happens:

Figure 141 Net Sales in Euros dropped sharply while the original Net Sales (in Dollars) remained unchanged

Are you seeing what I’m seeing? This is a pivot where the user/consumer can dynamically input parameters (via slicers) and have those parameters reflected in calculations!

This is absolutely real, and it’s simple to build.

Adding a “Parameter Harvesting” Measure

I’m going to ignore one of my own recommendations here and create a table via copy/paste. I feel ok about doing so, because this is a table that isn’t going to change frequently (if at all), and I’m not going to write a bunch of formulas on this table (so if I needed to recreate it later, it would not be difficult to do).

I create a single column table in Excel. This is going to be the basis for my “USD per EUR” (dollars per euro) slicer: And then paste as new table in PowerPivot, yielding:

Figure 142 Copying a simple table of data from Excel and Pasted as Exch Rates table in PowerPivot

Now we can create a new pivot, and put that column on a slicer:

Figure 143 New pivot, Category on Rows and the newly-pasted table/column on a slicer

Because I most often use Disconnected Tables as parameters, and those parameters are usually exposed as slicers, you may also think of them as “Slicer Tables” or “Parameter Tables.”

Adding a “parameter harvesting” measure

Now we’re going to do something interesting: we’re going to add a measure on the Exch Rates table. This will be the first (but not last!) time that we create a measure on a non-data table.

The measure is:

[EURUSD] =

MAX(‘Exch Rates’[USD per EUR])

And the result:

Figure 144 Measure that returns $1.95 all the time? Why would I want such a thing?

The “punchline” here is that when I make a selection on the slicer, something neat happens:

Figure 145 The measure returns whatever is selected on the slicer!

Cool! But this is just regular old filter context doing its thing. Before the arithmetic (MAX) runs, the Exch Rates table gets filtered by the pivot, and the pivot is saying “[USD per EUR]=$1.45.”

Because only a single row is selected when the user picks a single slicer value, I could also have used MIN() or AVERAGE() or even SUM() as the aggregation function in my [ExchangeRateEURUSD] measure – they all return the same result when a single value is selected. Your choice of function in cases like this is partly a matter of personal preference and partly a question of how you want to handle cases where the user picks more than one value. You can even decide to return an error – which we will cover in a later chapter.

The Field List is Grumpy About This

At this point, the field list is giving me a warning:

Figure 146 Yes, there is no relationship between my Products table (where the Category field comes from) and my Exch Rates table (where this new measure comes from)

This warning, alas, merely goes with the territory of using Disconnected tables. And I don’t like sacrificing real estate in my field list to a warning that tells me nothing. So I tend to turn this warning off using the PowerPivot ribbon in Excel:

Figure 147 Toggle this button to disable that warning

Using the Parameter Measure for Something…Useful

OK, the [ExchangeRateEURUSD] measure is neat and all, but having a measure that tells the user what they’ve selected is of course pretty useless :-)

But now we can use that measure in other measures:

[Net Sales – EUR Equivalent] =

[Net Sales] / [EURUSD]

Figure 148 New measure tells me what my sales would look like in Euros at the selected exchange rate!

I even used the formatting options in the measure editor to format the new measure in Euros. Oddly satisfying.

And I don’t need the parameter measure displayed in order for it to work, so now I remove it to clean up the pivot:

Figure 149 Remove the parameter measure to produce a cleaner report

Add the Year column from the Sales table as a second slicer:

Figure 150 Year slicer works like it always has

Parameter Table Can Be Used on Rows and Columns too!

For grins, clear the slicer selection so that all exchange rates are selected, then drag that column to Rows instead:

Figure 151 Disconnected “slicer” field works on Rows too!

Are you surprised this works on Rows too? It felt weird to me the first time I did this, but it shouldn’t have. Each measure cell corresponds to a single value of the Exch Rate column. This is no different from using a normal column (one that IS connected to the Sales table via relationship, or is IN the Sales table) on Slicers versus Rows.

OK the result above is a little hard to see, let’s rearrange a bit:

Figure 152 Easier to see now with Category on slicer – EUR Equivalent Sales go down as Exchange Rate goes up

That may seem counterintuitive but it is accurate: if your country’s currency is worth a lot relative to other countries’ currencies, you make less money selling your products overseas than when your currency is worth less. So in some sense it’s “better” for a country’s currency to be worth less (and worse in other ways), but that’s not exactly a DAX topic now is it? I just didn’t want you thinking that I messed this one up :-)

Why is it Important That They Be Disconnected?

What would happen if our Exch Rates table were related to, say, the Sales table?

Short answer: nothing good. What column would we use to form the relationship? There isn’t a column in the Sales table that matches the values in the Exch Rates table. We could invent one I suppose, but then we’d have to arbitrarily assign transaction rows to individual exchange rate values, which would be nonsense.

And then when the user selected an exchange rate on the slicer, not only would that impact the [ExchangeRateEURUSD] measure (as desired) but it would also filter out rows from the Sales table (not desired). We’d undercount our sales figures, and in completely random fashion.

In real life, something like exchange rate is completely separate from Sales, so it shouldn’t surprise us really that we can’t create a meaningful relationship between them.

A Very Powerful Concept

There are many variations on disconnected tables. In fact this concept borders on infinitely flexible. We will return to this topic and cover a few more variations as the book progresses. Let’s look at one right now in fact.

Disconnected Table Variation: Thresholds

In the previous example, we used a disconnected table to inject a numerical parameter into certain calculations, and give the report consumer/user control over that parameter.

Now let’s try another example: giving the user control over “cutoffs,” or thresholds, in terms of, say, which products should be included and which shouldn’t.

Again, let’s work backwards by showing you the desired result first:

Figure 153 This pivot shows us, for instance, that there are 20 different products under the Clothing category that list for $50 or higher, and they accounted for $193k in sales.

Nifty huh? The “how to” starts out just like the last example:

Create a Disconnected Table to Populate the Slicer:

Figure 154 Another disconnected table

Write a Measure to “Harvest” the User’s Selection:

[MinListThreshold] =

MAX(MinListPrice[MinListPrice])

Figure 155 “Harvester” measure [MinListThreshold] created on the disconnected table

Diverging From the Prior Example: We Need to Filter, Not Perform Math

Hmm, now what? Last time, at this point we just divided an existing measure by our parameter measure to create something new. This time though, math isn’t going to do it.

Since we need to filter out Products unless they fit our criteria, we need to use our friend, CALCULATE().

And hey, CALCULATE() supports the “>=” operator, so let’s go ahead and do:

[Products Sales Above Selected List Price] =

CALCULATE([Total Sales], Products[ListPrice]>=
[MinListThreshold])

Enter it into the measure editor:

Figure 156 [Products Sales Above Selected List Price] entered into measure editor

And click Check Formula:

Figure 157 Error: A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.

That’s a terribly-worded error message. In my opinion, here is what that error message should say:

‘An expression was provided on the right side of a <filter> argument to CALCULATE. Only static values like 6 or “Red” are allowed in that location.’

CALCULATE() requires that you provide a static value on the right side of a <filter> expression.

CALCULATE() Has a Limitation? Not really.

Hard to believe isn’t it? CALCULATE never fails us!

Well it’s not failing us now either. It’s actually protecting us, and there is a version of this formula that works:

[Products Sales Above Selected List Price] =

CALCULATE([TotalSales],
FILTER(Products,
Products[ListPrice]>=[MinListThreshold]
)
)

What is the FILTER() function, and what is it doing occupying one of our <filter> arguments to CALCULATE?

FILTER() is the next function on your PowerPivot journey. And while it’s pretty straightforward, I don’t want to “hide” it in this chapter. It deserves its own. So we will come back to this threshold example, but we will do it in the context of the FILTER() chapter.