Measure outside of the pivot

  • 4 March 2019
  • 4 replies

Userlevel 7
Badge +1

I hope the topic fits in the table calculations category.

To make the example simple imagine I have a table of number of sessions by URL and then I add a pivot for a boolean flag.

How do I create a measure that only shows once as a difference of the first from the second (or the other way)?

Pivot_offset would give me a custom measure, of course, but it will show twice in the result table with the first (or second) column empty.

Any ideas?

4 replies

Userlevel 3


Yes table calculations seems appropriate here, although you could also easily achieve this in the model.

If you want to use a table calculation then look at the pivot_where function, You can use this to create one measure, rather than a measure per pivot.

The syntax would be

pivot_where(${dimension}=yes,${measure_1}) - pivot_where(${dimension}=no,${measure_2})


Userlevel 7
Badge +1

Oh! That’s an interesting approach. Thanks, I will give it a go and report back!

I checked and even by entering this simple formula:

pivot_where(${beforeafter}="1. After", ${measure})

it says:

The first argument for "pivot_where" must be a measure or a pivoted dimension

That before/after is custom dimension (string)

– another edit

When I saved it, it still worked but it was still showing the above error. Perhaps it has to do something with the fact that I used custom dimension here?

Userlevel 3

I’ve checked and I see the same error message, though you’re right that it does work in the end. It looks like its caused by the pivot dimension being a custom dimension rather than an actual dimension. Looks like a bug to me.


Userlevel 7
Badge +1

Yes, good to know that it’s sometimes good to click SAVE anyway to see if it works, in case error is a false positive 🙂