Simple SQL to DAX

[dennis_yin]

I need help translating this query to DAX:

select sum(true_total) as physician_values, p.physician_id
from orders as o inner join prescriptions as p
on cast(o.purchased_on as date) <= p.script_end and cast(o.purchased_on as date) >= p.script_start and o.client_id = p.client_id
group by p.physician_id;

I tried using DirectQuery but I was having trouble slicing the data by date.

[v-jiascu-msft]

Hi @dennis_yin,

The measure could be like below. If you need a more precise solution, please provide a dummy sample. 

The o.client_id = p.client_id part will be achieved by the relationship.

Measure =
CALCULATE (
    SUM ( orders[true_total] ),
    FILTER (
        orders,
        orders[purchased_on] <= MIN ( prescriptions[script_end] )
            && orders[purchased_on] >= MIN ( prescriptions[script_start] )
    )
)

Best Regards,

Dale

[dennis_yin]

Thanks for the reply Dale, but I realize now that I didn't properly explain what I'm trying to do. I need the sum of orders[true_total] for each unique physician, and I need to be able to filter valid prescriptions by a given date. So then if a user chooses September 1 with the date slicer, the true_total value for each physician only reflects prescriptions with a start date <= September 1, and an expiry date >= September 1.

[v-jiascu-msft]

Hi @dennis_yin,

Please share a dummy sample.

Best Regards,

Dale