Forum Discussion
Simple SQL to DAX
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_...
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
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.