Comparing values from opposite sides of a bridge table

[Ondra]

Hello!

Im trying to simulate the following SQL:

SELECT *

FROM orders AS `o`

INNER JOIN `order_content` AS `oc`
   ON `oc`.`orderId` = `o`.`id`

INNER JOIN `services` AS `s`
   ON `oc`.`serviceId` = `s`.`id` 
LEFT JOIN `service_prices` AS `sp`
   ON `sp`.`serviceId` = `s`.`id` AND `o`.`internalProjectId` = `sp`.`internalProjectId` 

I need to calculate several measures using the highlighted condition, however since the tables arent directly connected I cannot use RELATED. Any idea how to do this?

Thanks.

[Zubair_Muhammad · Accepted solution]

HI @Ondra

You can use TREATAS function to filter crm_orders

As a MEASURE

MEASURE =
CALCULATE (
    [YourMeasure],
    TREATAS (
        VALUES ( crm_service_prices[internalprojectid] ),
        crm_orders[internalprojectid]
    )
)

As a calculated Table

Calculated Table =
CALCULATETABLE (
    crm_orders,
    TREATAS (
        VALUES ( crm_service_prices[internalprojectid] ),
        crm_orders[internalprojectid]
    )
)

[Ondra]

Brilliant!