3

I want to create a table with a filter for use to select and compare things:

Say I have a variable Var, containing values A, B, C, D, E. I want to have a filter so that an user can select one of A B C D, meanwhile E is always selected. So the selected one and the fixed E can be display in one single table.

What is the best approach to achieve this (I checked other posts and they seem not working)?

Alan H
  • 33
  • 1
  • 3

1 Answers1

4

One easy solution if the choices for your variable are relatively stable is as follows:

  1. Create a parameter called say, Chosen_Var, containing the values that you want the user to choose between (i.e. A, B, C, D). Parameters can hold a single value.
  2. Create a calculated field called say, Var_Desired, to distinguish whether an individual data row meets your filter criteria [Var] = "E" or [Var] = [Chosen_Var]
  3. Place that field, [Var_Desired], on the filter shelf and select only the True value
  4. Show your parameter control and configure as desired

This will allow users to select one of the values A, B, C or D, and then filter to only include data where [Var] = the fixed value E or the value the user selected.

If the set of legal values changes frequently, so that using a static list of parameter values is difficult, or if you want the user to be able to select multiple values, you'll need another solution.

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • 1
    One thing I would add, which was not immediately obvious to me: To apply the parameter across multiple worksheets in a dashboard 1. add the [Chosen_Var] parameter to each worksheet 2. add the [Var_Desired] filter in the filter shelf in each worksheet 3. add the parameter to the dashboard - it'll work across every worksheet that has the [Var_Desired] filter and the [Chosen_Var] parameter – user1899231 Aug 10 '20 at 17:11
  • Yes. FYI, Step 1 is optional. Usually no reason to include a parameter on a worksheet. The workbook holds the parameter value. For step 2, there is a right click shortcut to apply a filter to multiple sheets, such as all sheets on a dashboard or all sheets using a particular data source. – Alex Blakemore Aug 10 '20 at 19:55