5 Replies Latest reply on May 9, 2016 3:11 AM by Simon Runc

# Filters and hierarchies for non-additive measures

Hey!

I have a question that I've been trying to solve within Tableau for the last hours, but couldn't:

Let's assume I have a table that stores different user_ids and whether that user has done a specific action on a given day or not, based on additional boolean columns for each action.

The tricky part is, that these actions can be combined into different buckets on different levels and every user could have performed an arbitrary combination of the actions, such as:

Given actions {aa, ab, ac, ad, ba, bb, bc, ca, cb, cc}, a user is in bucket

- A, if that user has done at least one of the actions {aa, ab, ac, ad}

- B, if that user has done at least one of the actions {ba, bb, bc}

- C, if that user has done at least one of the actions {ca, cb, cc}

- X, if that user has done any of all available actions .

This can be further categorised into levels of 1 = {X}, 2 = {A, B, C}. 3 = {aa, ab, ac, ... , cc}

I want to have a parameter that let's you choose the level so that the graph only displays the Number of users, satisfying the corresponding conditions of that level.

E.g. If I pick level 2, I want to see the number of unique users split into buckets A, B and C, for level 3, I want to see the number of unique users split into aa, ab, ac, ... , cc.

I tried solving it with Parameters and a Case Statement in a Calculated Field, however, Case Statements are mutually exclusive in that they check for the first condition, take all the rows that satisfy this condition and only use the remaining rows to evaluate the next condition. I would need something that does something along the lines of: 'If [Parameter].[level] = 1 THEN "only display these Measures" , etc.

I attached the problem in all different fomats.

Thanks! A

• ###### 1. Re: Filters and hierarchies for non-additive measures

Hi Andy Truong,

That's a very interesting problem. I tried to come up with a different approach but didn't succeed.

I am still working on it, but in the meantime maybe one of our amazing ambassadors could help you? Toby Erkson?

• ###### 2. Re: Filters and hierarchies for non-additive measures

Thanks Lenny for the confidence vote but Rody Zakovich or Simon Runc or Mark Fraser would be one of my first choices off the top of my head.  Actually, any of the other Ambassadors besides me   I'm better at server admin stuff.

• ###### 3. Re: Filters and hierarchies for non-additive measures

hi Andy,

cheers for the ping Toby

So I'm going to start here by saying that your data isn't in the optimal shape for Tableau (in fact any database-based software, of which Tableau is one), which is part of the reason you are finding this so tricky...

In the attached I've used Tableau (built in Pivot) feature to pivot your data, so by selecting your columns and selecting the pivot feature we go from this

to this

We could rename the Pivot Fields Name and Pivot Fields Value, but I've left as is so you can see what's going on.

Once I have in this format, I can create the following calculated field

[Level to Show]

IF [Levels]=1 THEN 'Level 1'

ELSEIF [Levels] = 2 THEN LEFT([Pivot field names],1)

ELSEIF [Levels] = 3 THEN [Pivot field names]

END

and then I can bring this field onto my columns, and the SUM of Pivot Field Values to the rows...and hopefully this gets you to what you need.

Hopefully you can see why having the data in this (down-page) shape makes life much easier...It's also much more scalable, say you update your data and you now have ae, be, ce, and de...with the data as is, you'd need a whole new set of formulas to handle the new additions, where as the formula above will handle this without any adjustment. Not to mention the setting up, which in its current form, will need (as you've found) a formula for every aa, ab, ac....etc.

2 of 2 people found this helpful
• ###### 4. Re: Filters and hierarchies for non-additive measures

Hey Simon Runc,

I'll stick to that approach then.

I think a problem like this could be easily solved by allowing sub-filtering the 'Measure Names' Filter. For instance, when I'm using 'Measure Names' under 'Marks' and 'Measure Values' under 'Rows' I can easily de-/select whatever measure I want to see by showing the 'Measure Names' Filter as a Multiple Value List . What I mean with sub-filtering is a way to pick only a subset of the 'Measure Names' options, e.g. a Custom Parameter that allows 'Multiple Selection' in which you can specify rules on how the 'Measure Names' Filter should behave.

This is actually achievable by using only the measures you want and then selecting 'Hide All Unused Fields', however the downside of it is that you have to create a new connection to the same data source for every instance in which you have a different selection of measures. To achieve multiple levels of hierarchy I could in theory create a worksheet for all different combinations, and have a Parameter on the Dashboard pick the Worksheet with the desired hierarchy level, but I agree that just creating 'deep' table is a much easier solution.

Thanks,

A

• ###### 5. Re: Filters and hierarchies for non-additive measures

hi Andy,

Yes I see your point, and the problem really lies in that the Tableau generated Measure Names, isn't a full dimension...in that you can't access it via a calculated field (eg. IF CONTAINS([Measure Names], 'a') THEN ....END), so pivotting them to create full dimensions is the only real way to achieve this currently.

There are a couple of ideas which would help this, so please vote them up

as you can see they are quite popular requests...fingers crossed Tableau is listening!!

1 of 1 people found this helpful