7 Replies Latest reply on Jan 6, 2015 10:56 AM by Bill Lyons

# Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

Say I have 10 measures, called A, B, C, D, E, F, G, H, I, and J. I want to give the user the ability to choose what they want to see, but the measures are all related in a certain way. Measures B, D, F, H, and J are all functions of the previous measure (A, C, E, G, and I, respectively). In this specific case, the second measures are percent differences of the other one. I want the user to be able to filter (using a quick filter) between measures A, C, E, G, and I, and have it show either A&B, C&D, E&F, G&H, or I&J.

Is that possible using a parameter or somehow tying measures together?

• ###### 1. Re: Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

Based on your description, I think I would use a parameter, e.g.: Then do the math in the calculated field, e.g.:

case [Parameter 1]

when "A&B" then A/B

when "C&D" then C/D

when "E&F" then E/F

when "G&H" then G/H

when "I&J" then I/J

end

Is that what you are looking for?

bl

1 of 1 people found this helpful
• ###### 2. Re: Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

Yes, this is the first step, but how do I filter on those values? Right now, I am filtering on Measure Names (the hypothetical A, C, E, G, and I) like below, but I can't figure out how to use the values from the calculated field to show two measures. In your example, the calculated field could give me C/D for example, but that would be the measure C divided by the measure D, no? I need the calculated field to give me two measures, as if I had selected two measure names to view from the image below. • ###### 3. Re: Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

I have an idea that I am trying now...create two separate calculated members that are dependent on the same parameter so that two measures always show up.

1 of 1 people found this helpful
• ###### 4. Re: Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

So I got it to work, although now my problem is that different measures need to be formatted differently. Some should aggregate as averages and be represented as a percentage, and the others should all be summed, although some need to be represented as money. Can I have a calculated field whose formatting changes depending on the calculation? Or is there a way to choose the formatting based on a parameter?

• ###### 5. Re: Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

Sounds like you have tried what I would have suggested: two separate calculated members based on the same parameter.

I do not know of any way to change formatting based on a parameter. I have similar situations where a parameter selects a monetary value, a number, or a percentage. I had to be satisfied with not including the "\$" or "%" symbols, and just show numbers, and had to multiply the percentage by 100. I suppose you could convert everything to strings and format them manually.

Glad you are making some progress.

bl

1 of 1 people found this helpful
• ###### 6. Re: Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

Thanks. Looks like I'm going to create a different sheet for each set of measures and then have the end user filter by choosing which sheet to use. Kind of a lot more work and likely more memory intensive, but there doesn't seem to be a better way unless Tableau implements a FORMAT() function (something like this idea http://community.tableau.com/ideas/1910).

For reference, other possibilities I found were to format everything as a string so that I could make sure each thing showed up how I'd like it (see Unable to Change Number Format When Using Parameters to Swap Measures | Tableau Software and Can Formatting be Changed Based on a Parameter?), but then the value is no longer a number so you lose a lot of functionality there. This (Creating a Sheet Selector for a Dashboard | Tableau Software) is the solution I am going with.

• ###### 7. Re: Can I have a filter that allows the user to see what measures are shown (but tie certain measures together)?

I agree, and voted up that idea. Thanks for pointing that out.