How can I hide a value in filter if I need to use this value in a calculated field?

Question & Answer
, , , , , ,
1

Hi All,

I have a field that I use for filter, which contains 3 values ​​(A, B and C).

I need to show only values ​​A and B in the filter, hiding C, but I need to use value C in a calculated field.

How can I hide the C value of the filter, when I need to use it in a calculated field?

Example:
Filter:
A
B
C → need to hide, but if I hide it my calculated field does not work

My calculated field 1 :

distinct_countif({client_id}, {stage} = ‘1’ AND type = ‘A’ )
/
distinct_countif({client_id}, {stage} = ‘1’ AND in ({type}, [‘A’, ‘C’]) )

My calculated field 2 :

distinct_countif({client_id}, {stage} = ‘1’ AND type = ‘B’ )
/
distinct_countif({client_id}, {stage} = ‘1’ AND in ({type}, [‘B’, ‘C’]) )

Is there a way to do something about it:

if filter = ‘A’ then filter (‘A’,‘C’) ?

Thank you

2

Hi @July, there’s a way to achieve this maybe by allowing you to hide the ‘C’ value from your filter while still using it in your calculations.

Here’s an approach:

  1. Create a Parameter:
  • Create a parameter called “TypeFilter” (or a name of your choice).
  • Set the data type to “String”.
  • Define the allowed values as “A” and “B” (the values you want to show in the filter).
  • Add this parameter as a control (dropdown list) to your analysis.
  1. Modify Your Calculated Fields:
  • Update your calculated fields to incorporate the parameter and conditionally include ‘C’ based on the parameter’s value. Here’s how your modified calculated fields would look:
    Calculated Field 1: distinct_countif({client_id}, {stage} = '1' AND ( {type} = ${TypeFilter} OR (${TypeFilter} = 'A' AND {type} = 'C'))) / distinct_countif({client_id}, {stage} = '1' AND ( in( {type}, [${TypeFilter}, 'C']) OR ${TypeFilter} = 'A' ) )
    This includes ‘C’ only if the parameter is ‘A’. Otherwise it returns only the value selected in the filter.
    Calculated Field 2: distinct_countif({client_id}, {stage} = '1' AND ( {type} = ${TypeFilter} OR (${TypeFilter} = 'B' AND {type} = 'C'))) / distinct_countif({client_id}, {stage} = '1' AND ( in( {type}, [${TypeFilter}, 'C']) OR ${TypeFilter} = 'B' ) )
    This includes ‘C’ only if the parameter is ‘B’. Otherwise it returns only the value selected in the filter.
  1. Use the Parameter as the Filter:
  • Remove the original filter on the “type” field.
  • Instead, apply a filter using the “TypeFilter” parameter you created. This will limit the filter options to only “A” and “B.”

How it works:

  • The parameter controls which type(s) are visible in the filter.
  • The calculated fields adjust their logic to include ‘C’ if it’s relevant based on the filter selection.
  • Your visuals will respond to the parameter-controlled filter, effectively hiding ‘C’ while keeping it active behind the scenes for your calculations.

Additional Considerations:

  • You can customize the parameter’s appearance (dropdown, list, etc.) to fit your dashboard design.
  • If you have multiple visuals using these calculated fields, ensure they all reference the same parameter to maintain consistency.
  • Consider adding tooltips or explanations for your visuals to clarify the logic behind the filter and calculations.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

1 Like
4

Hi @July, hope this post helps:

1 Like