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

**URL:** <https://discourse.getdbt.com/t/how-to-join-to-an-other-table-in-the-measure-of-dbt-semantic-layer/16954>\
**Category:** Help\
**Tags:** jinja, dbt-core, semantic-layer\
**Created:** [November 13, 2024, 9:23am UTC](https://discourse.getdbt.com/t/how-to-join-to-an-other-table-in-the-measure-of-dbt-semantic-layer/16954 "2024-11-13T09:23:07Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![anhphuwowng2k](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/anhphuwowng2k/32/4697_2.png) [@anhphuwowng2k](https://discourse.getdbt.com/u/anhphuwowng2k)\
**Post date:** [November 13, 2024, 9:23am UTC](https://discourse.getdbt.com/t/how-to-join-to-an-other-table-in-the-measure-of-dbt-semantic-layer/16954/1 "2024-11-13T09:23:07Z")

</div>

## 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:

```auto
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.

```auto
 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” .

```auto
 - 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:

```auto
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.