Show/hide columns (measures) on looks based on condition (dimension)

[magda1]

Hi,

I am creating a dashboard and the looks composing it are several. One of them should show the same basic set of measures (let’s say Investment and Revenue) in all cases, and then show some more based on the value of a set of columns that are dimensions (let’s say platform and game).

For ex. when platform is 1 and game is 1 then I want the Look to show only Investment, Revenue and Retention.

When platform is 1 and game is 2 then I want the Look to show Investment, Revenue, Retention and Conversion.

I hope the question is clear. Is there any way to achieve that?

Thanks a lot.

[Eric_Lyons]

Hi magda,

We can use a table calculation with an if statement that results in yes or no to dynamically hide full line items by selecting hide no’s from visualization, but for this the columns seem to need to be dynamic.

A potential route we could pursue is to always show all three columns, but we can have dynamic values based on either a liquid if statement or a SQL case when. The dynamic field with a case when may look something like this. This specific example if based on user input from a parameter, but we can use the view_name.field_name._in_query parameter or view_name.field_name._is_selected  liquid syntax.

dimension: dynamic_timeframe {
  type: string
  sql:
    CASE
    WHEN {% parameter timeframe_picker %} = 'Date' THEN ${orders.created_date}
    WHEN {% parameter timeframe_picker %} = 'Week' THEN ${orders.created_week}
    WHEN{% parameter timeframe_picker %} = 'Month' THEN ${orders.created_month}
    END ;;
}

If statement example:

{% if view_name.field_name._in_query %}
  something to insert if true
{% else %}
  something to insert if false
{% endif %}

To clarify, if I have line one where platform is 1 and game is 1 we only show Investment, Revenue and Retention, but if on line 2 platform is 1 and game is 2, we want to show three fields:  Investment, Revenue, Retention and Conversion. Would the field not included in the first row just appear as blank or null? If the user manually selects the platform and the game values with either a parameter or filter, the dynamic field option could potentially work. 

Please let me know if you have any questions.

Thanks,

Eric