Shared filters/parameters between multiple views used in one visual

Hello,

we have two views on two different fact tables joined inside one explore (not the same granularity and dimensionality but having some shared/conformed dimension...imagine revenue & costs fact tables with shared dimension being for example months)

In both fact tables we have two fact columns representing values in USD and in EURO and we have in both views parameters pCURRENCY to select as user inside dashboard the right currency (the corresponding fact column dynamically via liquid.

in both views we have as example:

  parameter: pCURRENCY {
    label: "pCURRENCY"
    type: string
    allowed_value: {label: "USD"  value: “USD"    }
    allowed_value: {label: "Euro"  value: "EURO"    }
    default_value: "EURO"
  }

  measure: revenue{
    type: sum
    sql: CASE
          WHEN {% parameter pCURRENCY%} = "USD" THEN  ${TABLE}.USD ELSE ${TABLE}.EURO
        END;;

The challenge is that we have a tile (placed inside dashboard) which combine these two fact tables inside a single line chart with two lines corresponding to two measures combining revenue and costs and we want to expose the parameter inside the dashboard to switch the currency EURO to USD
UT today the only way to make it work is to expose tow different parameters the user needs to select (not just one common) from the two views as we are unable to make a link between them in lookml if its even possible and from dashboard its possible to apply the selection to only one parameter inside a tile.

The objective is obviously there will be only one filter/parameter in the dashboard to select the currency you want to work with not two of them.

Any idea about how to achieve that? Thank you

2 1 960
1 REPLY 1

Hi, 
I have same issue and have only 1 workaround that I'm using

U can use parameter in exprore and join according to it's value

Example where I switch timezone from UTC to PDT 

sql_on: ${reversal_calendar.report_date} =
{% if reversal_calendar.reporting_timezone._parameter_value == 'utc' %}
${f_reversal.reversal_date}
{% elsif reversal_calendar.reporting_timezone._parameter_value == 'pdt' %}
${f_reversal.reversal_date_pdt}
{% else %}
${f_reversal.reversal_date}
{% endif %};;

For your case this approach has it's drawback - u have to have every currency calculated in one measure column (say Revenue) and use join as mandatory filter. 
This is bad in some cases. I'd like to have 2 separate measures like Revenue USD and Revenue EUR separate in front of every row in fact table. Cause it's a good design.
If U use parameter in other view, U will have ML validation error. 


Top Labels in this Space
Top Solution Authors