How to join to an other table in the measure of dbt semantic layer ?

Help
, ,
1

I don’t know how to use the measure in the expr of an other measure.

I am required to convert sql scripts to dbt semantic layer.

This is origin sql:

Select 
        SUM(
                  CASE
                      WHEN fso.opportunity_type IN ('sale', 'optimal')
                      THEN COALESCE(fso.amount_total * c.rate, fso.amount_total_vn)
                      ELSE 0
                  END
            ) AS sale_rev
        FROM {src_fso} AS fso
        LEFT JOIN {src_der} AS c
            ON fso.country_type_id = c.country_type_id
            AND YEAR(fso.confirmed_datetime + INTERVAL '7' HOUR) = c.year
            AND MONTH(fso.confirmed_datetime + INTERVAL '7' HOUR) = c.month
 GROUP BY 1, 2, 3, 4

I think this agg session should be a measure in semantic model.

 SUM(
                CASE
                    WHEN fso.opportunity_type IN ('sale', 'optimal')
                    THEN COALESCE(fso.amount_total * c.rate, fso.amount_total_vn)
                    ELSE 0
                END
) AS sale_rev

However, it use ‘rate’ column for calculating which is belong to other table. I tried user “Measure” .

 - name: sale_rev
        description: "Tổng số tiền của đơn hàng sale và optimal theo VND"
        expr: CASE
                  WHEN opportunity_type IN ('sale', 'optimal')
                  THEN COALESCE(amount_total * {{ Measure('exchange_rate') }}, amount_total_vn)
                ELSE 0
              END
        agg: sum
        label: "Sale rev"
        create_metric: true

It throw error:

Compilation Error
  Could not render CASE WHEN opportunity_type IN ('sale', 'optimal') THEN COALESCE(amount_total * {{ Measure('exchange_rate') }}, amount_total_vn) ELSE 0 END: 'Measure' is undefined

Please help me.