4 Replies Latest reply on Jun 28, 2016 11:45 AM by Eliran Epshtein

# How to create a filter of different sheet on a dashboard?

Hi Everyone,

I've created a dashboard of three sheets. I'd like to have only two sheets displayed at any given point of time. The first sheet should always be shown while the user gets to choose between sheet #2 or #3 (By creating a filter for these two). These two continuous line sheets share the same x-axis but have different dependent variables.

I know it sounds simple but I've searching online and couldn't find a solution that satisfies my problem.

I was wondering if anybody in this forum could help me out.

Thank you in advance,

Eliran

• ###### 1. Re: How to create a filter of different sheet on a dashboard?

Hi Eliran,

You only need two sheets for this. Create a parameter of the type string with values like these:

measure_a    Measure A

measure_b    Measure B

Now create a calculated field:

if parameter = "measure_a" then sum([measure_a])

elseif parameter = "measure_b" then sum([measure_b])

else null

end

On your second sheet put the time dimension on your columns to build out the x axis, then your newly created calculated field on the rows to build out your y axis. Show the parameter on the dashboard and voila. I have attached a sample workbook to demonstrate this technique

Best,

Fabian

InterWorks Europe

1 of 1 people found this helpful
• ###### 2. Re: How to create a filter of different sheet on a dashboard?

Fabian,

First, I'd like to thank you for the explanation, it does help a lot!

However, my problem is that my second sheet has one depended variable but the third sheet has two dependent variables. I created a parameter with only two options but I'm not sure how to add another measurement after the ELSEIF function when trying to create the calculation field.

IF[A or (B&C)]="A" THEN avg([A])

ELSEIF [A or (B&C)]="B&C" THEN {AVG([B]) AND AVG ([C])} /// It didn't show me two y-axis's as I'd like to get

ELSE NULL end

- I also tried creating two different calculated fields:

Calculated Field 1: A or B

Calculated Field 2: A or C

and then added them separately into my "Rows" space which gave me what I was looking for. Yet, it looked very messy on the dashboard once I concentrate on one point.

I'd appreciate it if you could supply me with the right typing method so I can get my desired result.

Thanks again,

Eliran

• ###### 3. Re: How to create a filter of different sheet on a dashboard?

Hey Eliran,

The trick is to have another if statement that defaults to null when measure a is selected in the parameter, and to measure c when measure b&c is selected in the parameter. Dual axing Measure A or B with the new field Null or Measure C will create the desired effect. I would advise you to take a look at both calculated fields I have marked with italics to get a better understanding of this mechanic.

P.S. you can right-click on the second y axis and deselect "show header" for a cleaner visualisation.

Best,

Fabian

InterWorks Europe

1 of 1 people found this helpful
• ###### 4. Re: How to create a filter of different sheet on a dashboard?

Thank you so much for your help, it looks better now.

Eliran